SQL Query - HELP

[ITGURU]

I have an in-house designed system where the following happens:

On each unique date, I have several SQL entries for each school year group. i.e:

 

07/06/2017 Year 7

07/06/2017 Year 7

07/06/2017 Year 8

07/06/2017 Year 9

07/06/2017 Year 10

07/06/2017 Year 10

08/06/2017 Year 7

 

I have an SQL query which can show the below using the query: SELECT DISTINCT year, COUNT(*) AS Count, date AS [Date] FROM tablename GROUP BY year, date order by date desc

 

Date, Year Count

07/06/2017 Year 7 2

07/06/2017 Year 8 1

07/06/2017 Year 9 1

07/06/2017 Year 10 2

 

However, What i'm looking for is a query to generate the following - so that for each date, I have a count column for each year group

Date, Year 7 Year 8 Year 9 Year 10

07/06/2017 2 1 1 2

08/06/2017 1

 

I am unable to get the right query/syntax to get this to show as required.

 

Any help much appreciated.

Thanks.

[webman]

If I was approaching this in MySQL, I might do something like the following. I have a feeling you might be using MSSQL or Access SQL, which I'm not overly familiar with the syntax differences, so you may need to adjust slightly.

 

SELECT
date,
SUM(IF(year = "Year 7", 1, 0)) AS "Year 7",
SUM(IF(year = "Year 8", 1, 0)) AS "Year 8",
SUM(IF(year = "Year 9", 1, 0)) AS "Year 9",
SUM(IF(year = "Year 10", 1, 0)) AS "Year 10",
SUM(IF(year = "Year 11", 1, 0)) AS "Year 11"
FROM
tablename
GROUP BY
date
ORDER BY
date DESC

 

Haven't tested it, but hopefully points you in the right direction.

[ITGURU]

If I was approaching this in MySQL, I might do something like the following. I have a feeling you might be using MSSQL or Access SQL, which I'm not overly familiar with the syntax differences, so you may need to adjust slightly.

 

SELECT
date,
SUM(IF(year = "Year 7", 1, 0)) AS "Year 7",
SUM(IF(year = "Year 8", 1, 0)) AS "Year 8",
SUM(IF(year = "Year 9", 1, 0)) AS "Year 9",
SUM(IF(year = "Year 10", 1, 0)) AS "Year 10",
SUM(IF(year = "Year 11", 1, 0)) AS "Year 11"
FROM
tablename
GROUP BY
date
ORDER BY
date DESC

 

Haven't tested it, but hopefully points you in the right direction.

 

Thanks! It gave me a rough idea , so cracked it by part internet search and part your example! Now got each date, each year group and the total!

 

SELECT date, COUNT(CASE WHEN year = 'Year 7' THEN 1

ELSE NULL

END) AS Year7

,COUNT(CASE WHEN year = 'Year 8' THEN 1

ELSE NULL

END) AS Year8 ,

COUNT(CASE WHEN year = 'Year 9' THEN 1

ELSE NULL

END) AS Year9 ,

COUNT(CASE WHEN year = 'Year 10' THEN 1

ELSE NULL

END) AS Year10,

COUNT(CASE WHEN year = 'Year 11' THEN 1

ELSE NULL

END) AS Year11

 

,COUNT(*) AS Total

FROM tablename WITH ( NOLOCK )

group by date

[webman]

Awesome!

[SimonHooker]

Fundamentally what you are doing is pivoting, which MS SQL does support I believe - might be worth having a read if you fancy a more elegant solution https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx