Count and Group by and Subqueries

# Thread: Count and Group by and Subqueries

1. Member
Join Date
Dec 1969
Posts
79

## Count and Group by and Subqueries

I always have a problem getting my head around group by&#039;s...<BR><BR>I am looking to get this result<BR><BR>=====================<BR>RESP_AREA MONTH FY02 FY03 CHANGE<BR>--------- ----- ---- ---- ------<BR>1.........JAN.....50.....40.....-10<BR>1.........FEB.....40.....60.....20<BR>1..... ....MAR.....30.....55.....15<BR>...<BR>4.........J AN.....50.....40.....-10<BR>4.........FEB.....40.....60.....20<BR>4..... ....MAR.....30.....55.....15<BR><BR>source table:<BR>P1_CODE..... OFF_DATE...RESP_AREA <BR>----------- ----------- ---------<BR>1......... 01-01-2002...1<BR>1......... 01-21-2002...1<BR>...<BR>3......... 02-11-2002...3<BR>3......... 03-21-2002 4<BR><BR><BR>So I am trying to group by months and counting by RESP_AREA (AS BAR)<BR><BR>Using this code I am getting a linear table starting with day one through today:<BR><BR>########################<BR>SELECT TOP 100 PERCENT RESP_AREA, DATEPART(M, OFF_DATE) AS MNTH, DATEPART(YY, OFF_DATE) AS YR, COUNT(*) AS BAR<BR>FROM OFFENSES<BR>WHERE (P1_CODE = 3 OR P1_CODE = 5 OR P1_CODE = 7) AND RESP_AREA IS NOT NULL<BR>GROUP BY RESP_AREA, DATEPART(YY, OFF_DATE), DATEPART(M, OFF_DATE)<BR>ORDER BY RESP_AREA, DATEPART(M, OFF_DATE), DATEPART(YY, OFF_DATE)<BR>###################<BR><BR>Can someone point me in the right direction on getting the individual year counts into their own columns?

2. Senior Member
Join Date
Dec 1969
Posts
96,118

...what is the point in the <BR> TOP 100 PERCENT<BR>??????<BR><BR>That just gets *all* the results. Same as if you left it out.<BR><BR>************<BR><BR>You say that gives you a linear table "starting with day one through today".<BR><BR>As I read that, you should be getting a tble that is organized BY MONTH, from day one through today.<BR><BR>Yes?<BR><BR>************<BR><BR>They only way you could get per-year columns like that in a standard SQL query would be to know ahead of time WHICH YEARS you wanted the results for. Since you show a CHANGE between the pair of years, I would *assume* that you wanted only TWO years, yes?<BR><BR>So you could do:<BR><BR>SELECT resp_area, MONTH(off_date), <BR> SUM( CASE WHEN YEAR(off_date)=2002 THEN 1 ELSE 0 ) AS Count2002,<BR> SUM( CASE WHEN YEAR(off_date)=2003 THEN 1 ELSE 0 ) AS Count2003,<BR> SUM( CASE WHEN YEAR(off_date)=2003 THEN 1 ELSE -1 ) AS Change<BR>FROM offenses<BR>WHERE P1_code IN ( 3, 5, 7 )<BR>AND resp_area IS NOT NULL<BR>GROUP BY resp_area, MONTH(off_date)<BR>ORDER BY resp_area, MONTH(off_date)<BR><BR>***********<BR><BR>You&#039 ;ll see some short cuts I took there: It silly to type<BR> DATEPART(YY,off_date)<BR>when <BR> YEAR(off_date)<BR>does exactly the same thing with less code.<BR><BR>And I used "IN" for the P1_code test, instead of the set of ORs that you did.<BR><BR>Does that work for you???<BR><BR><BR>

3. Member
Join Date
Dec 1969
Posts
79

## Thanks, but..

I appreciate the assist!<BR><BR>The TOP was left there by accident (I was trying all kings of queries).<BR><BR>However, I am getting a:<BR>Line 1: Incorrect syntax near &#039;)&#039;.<BR><BR>error and can&#039;t seem to figure why...

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Oops...I forgot the ENDs!

Each CASE *must* end with an END.<BR><BR>SELECT resp_area, MONTH(off_date), <BR> SUM( CASE WHEN YEAR(off_date)=2002 THEN 1 ELSE 0 END ) AS Count2002,<BR> SUM( CASE WHEN YEAR(off_date)=2003 THEN 1 ELSE 0 END ) AS Count2003,<BR> SUM( CASE WHEN YEAR(off_date)=2003 THEN 1 ELSE -1 END ) AS Change<BR>FROM offenses<BR>WHERE P1_code IN ( 3, 5, 7 )<BR>AND resp_area IS NOT NULL<BR>GROUP BY resp_area, MONTH(off_date)<BR>ORDER BY resp_area, MONTH(off_date)<BR><BR>Heh! You should have seen that one!<BR><BR>

5. Member
Join Date
Dec 1969
Posts
79

## You Rock!

You should be getting paid for this!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•