Counting Records with Case

[Mark Gambo]

I have a table that has a date field and an item field. I want to compare the number of items sold this to the amount sold last year. I know I have to use the Case statement but I can't remember how to do it

Data in Table1:
Date Item
1/1/2004 Apples
3/5/2004 Oranges
2/5/2003 Grapes
5/15/2003 Oranges


The output:

Item 2004 2003
Apples 1 0
Grapes 0 1
Oranges 1 1


Thank in advance!!!!

[brucevde]

I am assuming SQL Server but try this query.

Select 
   Item, 
   Sum(Case When Year(DateField) = 2004 Then 1 Else 0),
   Sum(Case When Year(DateField) = 2003 Then 1 Else 0)
From Table1
Group By Item

[vb_dba]

To build on brucevde's example(if you're using SQL Server), you could use the Year() and GetDate() functions so that you don't have to change the Year settings:

Select 
   Item, 
   Sum(Case When Year(DateField) = Year(GetDate()) Then 1 Else 0),
   Sum(Case When Year(DateField) = (Year(GetDate()) - 1) Then 1 Else 0)
From Table1
Group By Item

[Mark Gambo]

Bruce,
Thanks for the quick post. I had to add 'END' in each of the Case Statements. Otherwise it worked great!!

SELECT INCIDENT_TYPE, 
   COUNT(CASE WHEN Year(Received_Dt) = 2004 THEN 1 ELSE 0 [b]END[/b]) AS C2004, 
   COUNT(CASE WHEN Year(Received_Dt) = 2003 THEN 1 ELSE 0 [b]END[/b]) AS C2003
FROM dbo.INCIDENTS
GROUP BY INCIDENT_TYPE

I had to add 'END' in each of the Case Statments. Otherwise it worked great!!



Thanks

[Mark Gambo]

Thanks vb_dba

But I am going to dynamically create the SQL statements using VB-Script. I like your solution, I never would have thought of that.



Mark