Condition Record Selection Based on Count

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

Condition Record Selection Based on Count

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-13 : 13:19:10

I have the following script which correctly totals up all records based on whetther they are not validated, approved, approved with erorrs or investigating.

What I want to do is modify the script so if the count of total_notval or total_invest so only approved records are summarized. I believe I need a select within a select, but not sure how to approach. Sample output is below

SELECT SUPERVISOR.SUP_NAME, SUPERVISOR.SUP_NO,
SUM(CASE WHEN approved_state = 0 THEN 1 ELSE 0 END) AS total_notval,
SUM(CASE WHEN approved_state = 1 THEN 1 ELSE 0 END) AS total_app,
SUM(CASE WHEN approved_state = 2 THEN 1 ELSE 0 END) AS total_app_err,
SUM(CASE WHEN approved_state = 3 THEN 1 ELSE 0 END) AS total_invest,
FROM TIMESHEET_DATA gd LEFT OUTER JOIN SUPERVISOR ON SUPERVISOR.SUP_NO = gd.supervisor_no
WHERE (gd.week_ending_date = '5/8/2009') AND (SUPERVISOR.SUP_NAME <> '')
GROUP BY SUPERVISOR.SUP_NAME, SUPERVISOR.SUP_NO

The current results are:

SUP_NAME SUP_NO total_notval total_app total_app_err total_invest
Jfff 13 4 5 0
Steve 0 6 4 0
Jim 0 6 0 1
Mary 0 2 0 0

The results should only display totals when total_notval = 0 or total_invest = 0:

Steve 0 6 4 0
Mary 0 2 0 0

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-05-13 : 13:23:23

easy way to do is wrap it with full select:

select * from 
(SELECT SUPERVISOR.SUP_NAME, SUPERVISOR.SUP_NO,
SUM(CASE WHEN approved_state = 0 THEN 1 ELSE 0 END) AS total_notval,
SUM(CASE WHEN approved_state = 1 THEN 1 ELSE 0 END) AS total_app,
SUM(CASE WHEN approved_state = 2 THEN 1 ELSE 0 END) AS total_app_err,
SUM(CASE WHEN approved_state = 3 THEN 1 ELSE 0 END) AS total_invest,
FROM TIMESHEET_DATA gd LEFT OUTER JOIN SUPERVISOR ON SUPERVISOR.SUP_NO = gd.supervisor_no
WHERE (gd.week_ending_date = '5/8/2009') AND (SUPERVISOR.SUP_NAME <> '')
GROUP BY SUPERVISOR.SUP_NAME, SUPERVISOR.SUP_NO)t
where total_notval = 0 or total_invest = 0

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-05-13 : 13:25:57

Thanks. Worked perfectly just needed the syntax

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources