help on count rows. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help on count rows.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-30 : 07:53:27

Hi.


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:


SELECT  count(*)
FROM ZZ_vwpProgressBoard
GROUP BY Film_strTitleAlt

as they are multiple films and i get 89 instead of 22 films.
this:


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).
thanks.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-10-30 : 09:03:58

[code]
-- *** Consumable Test Data ***
-- Please provide this in future
CREATE TABLE #t
(
Film_strTitleAlt varchar(20) NOT NULL
,Admissions int NOT NULL
,CoolAdmissions int NOT NULL
)
INSERT INTO #t
VALUES ('Film1', 300, 20), ('Film1', 100, 200)
,('Film2', 299, 19), ('Film2', 99, 199)
,('Film3', 298, 18), ('Film3', 98, 198)
,('Film4', 297, 17), ('Film4', 97, 197)
,('Film5', 296, 16), ('Film5', 96, 196)
,('Film6', 295, 15), ('Film6', 95, 195)
,('Film7', 294, 14), ('Film7', 94, 194);
-- *** End Test Data ***

-- Not sure what you want but play with the following:
-- Query 1
WITH Admissions
AS
(
SELECT Film_strTitleAlt
,SUM(Admissions + CoolAdmissions) TotalAdmissions
FROM #t
GROUP BY Film_strTitleAlt
)
,AdmissionOrder
AS
(
SELECT Film_strTitleAlt, TotalAdmissions
,ROW_NUMBER() OVER (ORDER BY TotalAdmissions DESC) AS rn
FROM Admissions
)
SELECT Film_strTitleAlt, TotalAdmissions
FROM AdmissionOrder
WHERE rn > 5;

-- Query 2
WITH Admissions
AS
(
SELECT Film_strTitleAlt
,SUM(Admissions + CoolAdmissions) TotalAdmissions
FROM #t
GROUP BY Film_strTitleAlt
)
,AdmissionOrder
AS
(
SELECT Film_strTitleAlt, TotalAdmissions
,ROW_NUMBER() OVER (ORDER BY TotalAdmissions DESC) AS rn
FROM Admissions
)
,OtherFilms
AS
(
SELECT
CASE
WHEN rn <= 5 THEN Film_strTitleAlt
ELSE 'Other Films'
END AS Film_strTitleAlt
,TotalAdmissions
,rn
FROM AdmissionOrder
)
SELECT Film_strTitleAlt
,SUM(TotalAdmissions) AS TotalAdmissions
FROM OtherFilms
GROUP BY Film_strTitleAlt
ORDER BY MIN(rn);

-- etc
[/code]

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-30 : 11:08:08

Hi. This will not run, gives error , also i just notice that i was working on the db from sql2008 but the actual db is sql2005, so i don't know if everything here will work.
Is there not a simple count query? Must i do all these?
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources