-
Oct 30th, 2014, 10:20 AM
#1
help on count rows.
Hi. SQL 2005.
Code:
SELECT TOP 5 Film_strTitleAlt
FROM ZZ_vwpProgressBoard
GROUP BY Film_strTitleAlt
ORDER BY Sum(Admissions+CoolAdmissions)desc
will give top 5 films. I want to get the total number or better yet i want to select all the other films except the top 5 (desc).
Can't do:
Code:
SELECT count(*)
FROM ZZ_vwpProgressBoard
GROUP BY Film_strTitleAlt
as they are multiple films and i get 89 instead of 22 films.
this:
Code:
SELECT count(*)
FROM ZZ_vwpProgressBoard
GROUP BY Film_strTitleAlt
will bring the 22 films but in 22 rows with different values.
I just need one row with the total or better yet, as i've said all the other films except the top 5 (desc).
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Oct 30th, 2014, 11:06 AM
#2
Re: help on count rows.
Hi,
What you need to do is to create a Query which identifies the Top 5 Results and then Excludes these results from a Second Query. As an example have a play with this:-
Code:
SELECT TOP (100) PERCENT dbo.ZZ_vwpProgressBoard.Film_strTitleAlt
FROM dbo.ZZ_vwpProgressBoard
LEFT OUTER JOIN
(SELECT TOP (5) Film_strTitleAlt
FROM dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1
GROUP BY Film_strTitleAlt
ORDER BY SUM(Admissions + CoolAdmissions) DESC) AS Top5Results
ON dbo.ZZ_vwpProgressBoard.Film_strTitleAlt = Top5Results.Film_strTitleAlt
WHERE (Top5Results.Film_strTitleAlt IS NULL)
GROUP BY dbo.ZZ_vwpProgressBoard.Film_strTitleAlt
Hope that helps.
Cheers,
Ian
-
Oct 31st, 2014, 03:12 AM
#3
Re: help on count rows.
Hi. Thanks, can i sum this up? something like:
Code:
SELECT sum(admissions) as admissions from(
SELECT TOP (100) PERCENT --dbo.ZZ_vwpProgressBoard.Film_strTitleAlt,
Sum(Admissions + CoolAdmissions) AS Admissions
-- Sum(CoolAdmissions) AS CoolAdmissions
FROM dbo.ZZ_vwpProgressBoard
LEFT OUTER JOIN
(SELECT TOP (5) Film_strTitleAlt
FROM dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1
GROUP BY Film_strTitleAlt
ORDER BY SUM(Admissions + CoolAdmissions) DESC) AS Top5Results
ON dbo.ZZ_vwpProgressBoard.Film_strTitleAlt = Top5Results.Film_strTitleAlt
WHERE (Top5Results.Film_strTitleAlt IS NULL)
GROUP BY dbo.ZZ_vwpProgressBoard.Film_strTitleAlt)
Ideally i would like the total in one row and it's percentage, so i can compare with other rows (well in this case the percentage would be 100% as is the only row)
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Oct 31st, 2014, 04:25 AM
#4
Re: help on count rows.
Hi,
Not sure which Percentage you are referring to but if you just want the Sum of Admissions then you can just say:-
Code:
SELECT TOP (100) PERCENT SUM(dbo.ZZ_vwpProgressBoard.CoolAdmissions + dbo.ZZ_vwpProgressBoard.Admissions) AS Admissions
FROM dbo.ZZ_vwpProgressBoard LEFT OUTER JOIN
(SELECT TOP (5) Film_strTitleAlt
FROM dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1
GROUP BY Film_strTitleAlt
ORDER BY SUM(Admissions + CoolAdmissions) DESC) AS Top5Results ON dbo.ZZ_vwpProgressBoard.Film_strTitleAlt = Top5Results.Film_strTitleAlt
WHERE (Top5Results.Film_strTitleAlt IS NULL)
Cheers,
Ian
-
Oct 31st, 2014, 05:29 AM
#5
Re: help on count rows.
Hey.
Ideally i would like to see. sum total , sum admissions, sum cooladmissions , percentage.
By percentage i mean the portion, percentage that all the above sum has in the the total or the WHOLE admissions, so also taken in consideration the TOP 5 that we missed here.
I am not sure if we can do:
1st top admission --- sum total , sum admissions, sum cooladmissions , percentage.
2nd top admission --- sum total , sum admissions, sum cooladmissions , percentage.
3rd top admission --- sum total , sum admissions, sum cooladmissions , percentage.
4th top admission --- sum total , sum admissions, sum cooladmissions , percentage.
5th top admission --- sum total , sum admissions, sum cooladmissions , percentage.
All the other admissions --- sum total , sum admissions, sum cooladmissions , percentage. (this is the above query).
I have made the all other admissions using the below but i am not sure how to incorporate the percentage and the other top 5 admissions. Or if is double with your sql above.
Thanks
Code:
WITH Admissions
AS
(
SELECT Film_strTitleAlt
,SUM(Admissions + CoolAdmissions) TotalAdmissions
,sum(Admissions) as Admissions
,Sum(CoolAdmissions) AS CoolAdmissions
FROM ZZ_vwpProgressBoard
GROUP BY Film_strTitleAlt
)
,AdmissionOrder
AS
(
SELECT Film_strTitleAlt, TotalAdmissions,Admissions,CoolAdmissions
,ROW_NUMBER() OVER (ORDER BY TotalAdmissions DESC) AS rn
FROM Admissions
)
select sum(TotalAdmissions) TotalAdmissions ,sum(admissions) admissions,sum(CoolAdmissions) CoolAdmissions FROM AdmissionOrder
WHERE rn > 5;
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Oct 31st, 2014, 08:55 AM
#6
Re: help on count rows.
I think you almost have it... in order to calc the pct though, you need the totals...
Code:
WITH Totals
AS
(
SELECT SUM(Admissions + CoolAdmissions) TotalAdmissions
,sum(Admissions) as Admissions
,Sum(CoolAdmissions) AS CoolAdmissions
FROM ZZ_vwpProgressBoard
)
,Admissions
AS
(
SELECT Film_strTitleAlt
,SUM(Admissions + CoolAdmissions) TotalAdmissions
,sum(Admissions) as Admissions
,Sum(CoolAdmissions) AS CoolAdmissions
FROM ZZ_vwpProgressBoard
GROUP BY Film_strTitleAlt
)
,AdmissionOrder
AS
(
SELECT A.Film_strTitleAlt, A.TotalAdmissions,A.Admissions,A.CoolAdmissions, (A.TotalAdmissions / T.TotalAdmissions) as PctAdmissions
,ROW_NUMBER() OVER (ORDER BY A.TotalAdmissions DESC) AS rn
FROM Admissions A
CROSS APPLY Totals A
)
select TotalAdmissions, Admissions, CoolAdmissions, PctAdmissions
FROM AdmissionOrder
WHERE rn <= 5
union
select sum(TotalAdmissions) TotalAdmissions, sum(Admissions) Admissions, sum(CoolAdmissions) CoolAdmissions, sum(PctAdmissions) PctAdmissions
FROM AdmissionOrder
WHERE rn > 5;
See if that works... I'm not wholly convinced it is what you need... but it should be dang near close.
-tg
-
Oct 31st, 2014, 09:40 AM
#7
Re: help on count rows.
Hi. I think you meant CROSS APPLY Totals T instead of CROSS APPLY Totals A ?
Also this will put the not top 5 film result, randomly somewhere in the rows. How would i go and pull it to the last or first row?
Thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Oct 31st, 2014, 09:52 AM
#8
Re: help on count rows.
Hi,
Now I understand exactly what you are trying to achieve this is how I would go about it:-
Create a query which Sums all the Admissions which can then be used to calculate the percentages.
Create the Top 5 Film Query with the sums that you need and then Cross Join the Query in Point 1 to calculate the Percentages for each of the Top 5.
Create the query which Sums all the other films Excluding the Top 5, Sum them to a single Record, and finally Cross Join the Query in Point 1 to once again calculate the Percentage of this group.
Use UNION to join the two Querys together to produce the final result.
This would then produce a final query something like:-
sql Code:
SELECT * FROM (SELECT TOP (5) ZZ_vwpProgressBoard_1.Film_strTitleAlt, SUM(ZZ_vwpProgressBoard_1.Admissions) AS TotalAdmissions, SUM(ZZ_vwpProgressBoard_1.CoolAdmissions) AS TotalCoolAdmissions, SUM(ZZ_vwpProgressBoard_1.Admissions + ZZ_vwpProgressBoard_1.CoolAdmissions) AS TotalGroupValue, SUM(ZZ_vwpProgressBoard_1.Admissions + ZZ_vwpProgressBoard_1.CoolAdmissions) / MAX(OverallTotalTable.TotalAdmissionValue) * 100 AS PercentageOfTotalAdmissionValue FROM dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1 CROSS JOIN (SELECT SUM(Admissions + CoolAdmissions) AS TotalAdmissionValue FROM dbo.ZZ_vwpProgressBoard) AS OverallTotalTable GROUP BY ZZ_vwpProgressBoard_1.Film_strTitleAlt ORDER BY TotalGroupValue DESC) As Top5ResultsTable UNION SELECT TOP (100) PERCENT MAX('All Other Films') AS OtherFilms, SUM(dbo.ZZ_vwpProgressBoard.Admissions) AS TotalAdmissions, SUM(dbo.ZZ_vwpProgressBoard.CoolAdmissions) AS TotalCoolAdmissions, SUM(dbo.ZZ_vwpProgressBoard.Admissions + dbo.ZZ_vwpProgressBoard.CoolAdmissions) AS TotalGroupValue, SUM(dbo.ZZ_vwpProgressBoard.Admissions + dbo.ZZ_vwpProgressBoard.CoolAdmissions) / MAX(OverallTotalTableAgain.TotalAdmissionValue) * 100 AS PercentageOfTotalAdmissionValue FROM dbo.ZZ_vwpProgressBoard LEFT OUTER JOIN (SELECT TOP (5) ZZ_vwpProgressBoard_1.Film_strTitleAlt, SUM(ZZ_vwpProgressBoard_1.Admissions) AS TotalAdmissions, SUM(ZZ_vwpProgressBoard_1.CoolAdmissions) AS TotalCoolAdmissions, SUM(ZZ_vwpProgressBoard_1.Admissions + ZZ_vwpProgressBoard_1.CoolAdmissions) AS TotalGroupValue, SUM(ZZ_vwpProgressBoard_1.Admissions + ZZ_vwpProgressBoard_1.CoolAdmissions) / MAX(OverallTotalTable.TotalAdmissionValue) * 100 AS PercentageOfTotalAdmissionValue FROM dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_1 CROSS JOIN (SELECT SUM(Admissions + CoolAdmissions) AS TotalAdmissionValue FROM dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_2) AS OverallTotalTable GROUP BY ZZ_vwpProgressBoard_1.Film_strTitleAlt ORDER BY TotalGroupValue DESC) AS Top5Results ON dbo.ZZ_vwpProgressBoard.Film_strTitleAlt = Top5Results.Film_strTitleAlt CROSS JOIN (SELECT SUM(Admissions + CoolAdmissions) AS TotalAdmissionValue FROM dbo.ZZ_vwpProgressBoard AS ZZ_vwpProgressBoard_3) AS OverallTotalTableAgain WHERE (Top5Results.Film_strTitleAlt IS NULL) ORDER BY PercentageOfTotalAdmissionValue DESC
Hope that helps.
Cheers,
Ian
-
Oct 31st, 2014, 10:16 AM
#9
Re: help on count rows.
Originally Posted by sapator
Hi. I think you meant CROSS APPLY Totals T instead of CROSS APPLY Totals A ?
Correct, sorry about that.
Originally Posted by sapator
Also this will put the not top 5 film result, randomly somewhere in the rows. How would i go and pull it to the last or first row?
Thanks.
They shouldn't be random... they should be the first 5 rows, followed by a sixth row which has all of the others... if you're not getting that, then something is interfering.
But...
Code:
select rn as SRC, TotalAdmissions, Admissions, CoolAdmissions, PctAdmissions
FROM AdmissionOrder
WHERE rn <= 5
union
select 6 as SRC, sum(TotalAdmissions) TotalAdmissions, sum(Admissions) Admissions, sum(CoolAdmissions) CoolAdmissions, sum(PctAdmissions) PctAdmissions
FROM AdmissionOrder
WHERE rn > 5
ORDER BY SRC
-tg
-
Oct 31st, 2014, 10:25 AM
#10
Re: help on count rows.
IanRyder that is great except i get zeros on percentages.
Tech looking at this now will post back...
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Oct 31st, 2014, 10:43 AM
#11
Re: help on count rows.
Tech this will bring zero percentages and also i am trying to have the >5 value on top but anyhow if this too much to ask i would probably cut this down inside VB.NET.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Oct 31st, 2014, 10:49 AM
#12
Re: help on count rows.
maybe it would help if I could SEE what you're getting... all I have to go by is what you're telling us. Unfortunately I'm more of a visual type where a picture is worth a thousand words. I'm not sure what you mean by zero percents, since they would be rolled (or should be) into line 6... if you want to filter out 0 percents, then you can do that anywhere along the line after the pct has been calc... or add it anywhere since the only reason the pct wqould be zero, is if TotalAdmissions is zero.... so you can add TotalAdmissions<>0 to any of the where clauses along the way - I would do it as early as possible so that your data sets are limited as quickly as possible in the chain.
A shot of what you're getting, what you expect, and maybe some sample data would be helpful
-tg
-
Oct 31st, 2014, 10:56 AM
#13
Re: help on count rows.
I get 0 on every percentage line.Tech, ok forget the percentages for now and everything else, as this is getting very complex for me.
I have shorted everything i guess and the only problem i have is the below and i would appreciate a simple solution for the union and i am through with this.
So i would like to join the 2 selects but when i use the ORDER BY, I get "ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator."
So i just need to know how will i bind that.No WITH no CROSS APPLY'S NO NOTHING. Just how would i get the union to work in the simplest way possible.
Thanks:
Code:
select CONVERT(VARCHAR,SUM(Admissions+CoolAdmissions)) as Cinema_DisplayName
,null AS Admissions,sum(CoolAdmissions) AS CoolAdmissions,null as Percentage from ZZ_vwpProgressBoard
Union
SELECT Cinema_DisplayName,
Sum(Admissions) AS Admissions,
Sum(CoolAdmissions) AS CoolAdmissions,
CASE
when
CHARINDEX('.',(LEFT(count(*) * 100.0 / (select count(*) from ZZ_vwpProgressBoard),5)),3) > 0 THEN LEFT(count(*) * 100.0 / (select count(*) from ZZ_vwpProgressBoard),5)
ELSE LEFT(count(*) * 100.0 / (select count(*) from ZZ_vwpProgressBoard),4)
END as Percentage
FROM ZZ_vwpProgressBoard
GROUP BY Cinema_DisplayName
-- if used will hit error
ORDER BY MAX(Cinema_Order)
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Oct 31st, 2014, 08:46 PM
#14
Re: help on count rows.
MAX(Cinema_Order) needs to appear in both select statements of the union. The ORDER BY applies to the WHOLE data set... not just the last one. The WHOLE set. So if you order by something, it has to EXIST in the first select and any other selects that you union. It will execute the first select, followed by the second, put the results together, then attempt to sort. That's why I had the "rn as SRC" and the "6 as SRC" and then the Order by SRC in the example I was giving.
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|