I am new to SQL, but I am trying to create a report where I can count the food purchases made by students and categorized by the Race/Ethnicity and choice of food every year.

For Example:

```
         2012                                          2013
Student Vegetarian     Meat           Unknown          Vegetarian     Meat           Unknown
---------------------------------------------------------------------------
Black    5 Purchases   4 Purchases      3 Purchases      5 Purchases   etc
White    4 Purchases   3 Purchases      3 Purchases      etc           etc
Asian    4 Purchases   1 Purchases      6 Purchases      etc           etc

```

While I was able to get the races in one column, I am lost at getting the count on different food categories. All I know is that the pur\_type is classified as "Veg", "Me" and "Ukt" for Vegetarian, Meat and unknown purchase.

Here is what I tried to do:

```
select 
     DATEPART(YYYY,pur_date) AS Year
    ,count (*) Citations
    , race
    , sex
    , ethnicity
    , GETDATE() as YTD
    ,COUNT(CASE WHEN pur_type = 'Veg' THEN 'Vegetarian' 
    WHEN pur_type = 'Me' THEN 'Meat' 
    WHEN pur_type  = 'UKt' THEN 'Unknown'  

         ELSE ''

         End as "Type"

, CASE 
    WHEN race='W' and sex='M' and ethnicity <> 'H' THEN 'Caucasian(Male)' 
    WHEN race='W' and sex='F' and ethnicity <> 'H' THEN 'Caucasian(Female)' 
    WHEN race='B' and sex='M' and ethnicity <> 'H' THEN 'African-American(Male)' 
    WHEN race='B' and sex='F' and ethnicity <> 'H' THEN 'African-American(Female)' 
    --WHEN race='A' and sex='M' and ethnicity <> 'H' THEN 'Pacific Islander(Male)' 
    --WHEN race='A' and sex='F' and ethnicity <> 'H' THEN 'Pacific Islander(Female)' 
    WHEN race='A' and sex='M' and ethnicity <> 'H' THEN 'Asian(Male)'  
    WHEN race='A' and sex='F' and ethnicity <> 'H' THEN 'Asian(Female)' 

    ELSE '' 
END as 'Race' 

from purchase
join purcharge
on purchase.purchaseid = purcharge.purchaseid
where pur_date between @startDate and  dateadd(DD, +1,  GETDATE())
and pur_type in ('Veg', 'Me', 'UKt')
GROUP BY DATEPART(YYYY,pur_date), race, sex, ethnicity , pur_type
ORDER BY 'Year','Race/Sex'

```

Looking for guidance. I am thinking maybe a sub query would work, maybe more case statements, but I am not able to have a count of how many food categories were purchased by students in each ethnic group.

Also,I am trying to get these results in an SSRS report so it may look slightly different in a SQL query.