nice sql query with aggregates

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 2000 Forums

Transact-SQL (2000)

nice sql query with aggregates

Author

Topic

sengoku
Starting Member

29 Posts

Posted - 2003-07-24 : 07:59:44

good afternoon :)

select recMPlat=(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=3 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),recPlat=(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=2 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),recGold=(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=1 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),albMPlat=(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=3 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),albPlat=(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=2 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),albGold=(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=1 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))

as you can see, this is a nice little query. :D what i would like to do is to simplify it so sql doesn't need to query each data_records and data_albums tables 3 times...

is there a neat way to select multiple aggregates (i want to count the number of records where rec_awards=1,2, and 3 seperately)? or would this be better off as a stored proc or something? or actually is this the most efficient i can get it? :D

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-24 : 08:25:39

quote:

select recMPlat=(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=3 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),recPlat=(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=2 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),recGold=(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=1 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),albMPlat=(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=3 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),albPlat=(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=2 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager)),albGold=(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=1 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))

as you can see...

No offence, but I can't see anything in that. Do you think you could reformat it, maybe with some spaces, and carriage returns..

-------
Moo. :)

sengoku
Starting Member

29 Posts

Posted - 2003-07-24 : 08:36:20

quote:

quote:

select recMPlat=
(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=3 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))
,recPlat=
(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=2 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))
,recGold=
(SELECT COUNT(rec_awards) from data_records with (nolock) where rec_awards=1 and rec_manag=@thisManager and rec_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))
,albMPlat=
(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=3 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))
,albPlat=
(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=2 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))
,albGold=
(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=1 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))

as you can see...

No offence, but I can't see anything in that. Do you think you could reformat it, maybe with some spaces, and carriage returns..

:D sure thing :D

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-07-24 : 09:00:59

Try this:

SELECT sum(recMPlat) 'recMPlat', sum(recPlat) 'recPlat', sum(recGold) 'recGold
sum(albMPlat) 'albMPlat', sum(albPlat) 'albPlat', sum(albGold) 'albGold'

FROM (

SELECT
sum(CASE WHEN rec_awards = 3 THEN 1 ELSE 0 END) 'recMPlat'
, sum(CASE WHEN rec_awards = 2 THEN 1 ELSE 0 END) 'recPlat'
, sum(CASE WHEN rec_awards = 1 THEN 1 ELSE 0 END) 'recGold'
, 0 'albMPlat'
, 0 'albPlat'
, 0 'albGold'

FROM data_records

WHERE
...put manager and band criteria here...

AND rec_awards between 1 and 3

UNION ALL

SELECT
0
, 0
, 0
, sum(CASE WHEN alb_awards = 3 THEN 1 ELSE 0 END)
, sum(CASE WHEN alb_awards = 2 THEN 1 ELSE 0 END)
, sum(CASE WHEN alb_awards = 1 THEN 1 ELSE 0 END)

FROM data_album

WHERE
...put manager and band criteria here...

AND alb_awards between 1 and 3

) x

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-24 : 09:27:00

quote:

quote:

quote:

select recMPlat=

,albMPlat=
(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=3 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))
,albPlat=
(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=2 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))
,albGold=
(SELECT COUNT(alb_awards) from data_albums with (nolock) where alb_awards=1 and alb_manag=@thisManager and alb_bandid not in (select band_au from data_bands where band_active=1 and band_manag=@thisManager))

as you can see...

No offence, but I can't see anything in that. Do you think you could reformat it, maybe with some spaces, and carriage returns..

:D sure thing :D

My untested attempt...

SELECT
sum (case when rec_awards=3 then 1 else 0 end) as recMPlat,
sum (case when rec_awards=2 then 1 else 0 end) as recPlat,
sum (case when rec_awards=1 then 1 else 0 end) as recGold
albMPlat,
albPlat,
albGold

FROM
data_records dr
LEFT OUTER JOIN
(SELECT band_au from data_bands where band_active=1 and band_manag=@thisManager) b
on b.band_au = dr.rec_bandid
LEFT OUTER JOIN
(

SELECT
sum (case when alb_awards=3 then 1 else 0 end) as albMPlat,
sum (case when alb_awards=2 then 1 else 0 end) as albPlat,
sum (case when alb_awards=1 then 1 else 0 end) as albGold
FROM
data_albums da
LEFT OUTER JOIN
(SELECT band_au from data_bands where band_active=1 and band_manag=@thisManager) b
on b.band_au = da.alb_bandid
WHERE
aln_manag=@thisManager and b.band_au is null

) albs
on dr.rec_bandid = albs.alb_bandid
WHERE
rec_manag=@thisManager and b.band_au is null

-------
Moo. :)

sengoku
Starting Member

29 Posts

Posted - 2003-07-24 : 09:53:16

excellent, excellent.

thanks to both of you, they both look very interesting and i shall go away and put my brain back into that wringer :)

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-24 : 10:30:47

Mine my have problems where you have bands with listed albums but no records. In that case you may need to create a table of band IDs to join from.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources