Selecting based on aggregate columns

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

General SQL Server Forums

New to SQL Server Programming

Selecting based on aggregate columns

Author

Topic

LaurieCox

158 Posts

Posted - 2008-10-01 : 11:04:53

I have the following tables:


CREATE TABLE #member(member_id int)
 
INSERT INTO  #member(member_id)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 

CREATE TABLE #Allergies(member_id int)
 
INSERT INTO  #Allergies (member_id)
SELECT 1 UNION ALL
SELECT 2 

CREATE TABLE #Diagnosis(member_id int)
 
INSERT INTO  #Diagnosis (member_id)
SELECT 2 UNION ALL
SELECT 3 

CREATE TABLE #rx (member_id int)

INSERT INTO  #rx (member_id)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 5

I want this query:


select member_id,
       (select count(*) from #Allergies a where m.member_id = a.member_id) as Allergies,
       (select count(*) from #Diagnosis d where m.member_id = d.member_id) as Diagnosis,
       (select count(*) from #rx r where m.member_id = r.member_id) as RX
  from #member m

To return only those rows where at least one of the counts is greater than 0

Adding this where clause:


select member_id,
       (select count(*) from #Allergies a where m.member_id = a.member_id) as Allergies,
       (select count(*) from #Diagnosis d where m.member_id = d.member_id) as Diagnosis,
       (select count(*) from #rx r where m.member_id = r.member_id) as RX
  from #member m
where (allergies > 0)
    or (diagnosis > 0)
    or (rx > 0)

Gives me these errors:


Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'allergies'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'diagnosis'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'rx'.

Expected results:


member_id  Allergies  Diagnosis  RX
1          1          0          1
2          1          1          1
3          0          1          0
5          0          0          1

Thanks,

Laurie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 11:09:52

[code]select *
from
(
select member_id,
(select count(*) from #Allergies a where m.member_id = a.member_id) as Allergies,
(select count(*) from #Diagnosis d where m.member_id = d.member_id) as Diagnosis,
(select count(*) from #rx r where m.member_id = r.member_id) as RX
from #member m
)r
where (allergies > 0)
or (diagnosis > 0)
or (rx > 0)[/code]

LaurieCox

158 Posts

Posted - 2008-10-01 : 11:10:12

Ok, I solved it:


select member_id, 
       Allergies,
       Diagnosis,
       rx
 from (select member_id,
             (select count(*) from #Allergies a where m.member_id = a.member_id) as Allergies,
             (select count(*) from #Diagnosis d where m.member_id = d.member_id) as Diagnosis,
             (select count(*) from #rx r where m.member_id = r.member_id) as RX
       from #member m) list
 where (allergies > 0)
    or (diagnosis > 0)
    or (rx > 0)

LaurieCox

158 Posts

Posted - 2008-10-01 : 11:11:24

Is this where I put in the little yak shooting Icon?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 11:17:19

quote:
Originally posted by AlgaeSea

Is this where I put in the little yak shooting Icon?

Mostly its done when somebody beats you to solution. so its an instance where you can put it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources