In SQL I need a count of ethnicity split into months

Question

I am using the following code to generate the number of different ethnic groups that we saw in a particular month.

SELECT
  COUNT(a.[_PatientId]) 'Quits set',
  a.Ethnicity
FROM (SELECT
        [_PatientId],
        SUBSTRING(CAST([_Ethnicity] AS VARCHAR),1,50) AS 'Ethnicity'
      FROM [Test].[dbo].[smoking_data$]
      WHERE [_Quit_Date] BETWEEN '2015-10-01' AND '2015-10-31'
     ) a
GROUP BY a.Ethnicity

which gives this result:

Quits set   Ethnicity
129         A - White British
1           B - White Irish
6           C - White other
1           F - Mixed White and Asian
2           G - Mixed Other Background
1           L - Asian/Asian British Other 
1           S - Any Other Ethnic Group
3           Unknown
1           Z - Declined

Is there any way for it to have the ethnicity as it is now, but a separate column of numbers for each month, rather than having to do it one month at a time?

I am very new to this, most of what I have done so far has been altering code that already existed. — Richard Dyke, Feb 25 '16 at 14:41

Bohemian · Answer 1 · 2016-02-26T11:53:33.683

1

Try this:

SELECT
    YEAR(_Quit_Date) AS Year,
    MONTH(_Quit_Date) AS Month,
    COUNT(*) 'Quits set',
    SUBSTRING(CAST([_Ethnicity] AS VARCHAR),1,50) AS 'Ethnicity'
FROM [Test].[dbo].[smoking_data$]
GROUP BY YEAR(_Quit_Date), MONTH(_Quit_Date), SUBSTRING(CAST([_Ethnicity] AS VARCHAR),1,50)
ORDER BY YEAR(_Quit_Date), MONTH(_Quit_Date)

Note: Untested.

edited Feb 26 '16 at 11:53

answered Feb 25 '16 at 17:03

Bohemian

412,405
93
575
722

Gives the error. Msg 8127, Level 16, State 1, Line 8 Column "Test.dbo.smoking_data$._Quit_Date" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. – Richard Dyke Feb 26 '16 at 10:59
@RichardDyke I had part of a previous version still in there. Try the newly edited version – Bohemian Feb 26 '16 at 11:54
That does work thank you. Would there be any way each month could be in a different column? – Richard Dyke Feb 26 '16 at 13:33
@RichardDyke yes - it's called a *pivot*. Search this site for "pivot" and "[sql-server-2008]", for example [see this](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server/15745076#15745076) – Bohemian Feb 26 '16 at 23:26

In SQL I need a count of ethnicity split into months

1 Answers1