CODEWITHSUNDEEP
sqlsql-servercountgroup-bydistinct
smartit
smartit

Reputation: 13

counting number of tickets for each month using SQL query

I have a table with name as Ticket with over than 1000 records such as this :

TicketNo  TicketDate  StateID 
1         01/01/2013  100
2         05/01/2013  100
3         09/01/2013  101
4         15/01/2013  101
5         17/01/2013  102
6         01/02/2013  100
7         05/02/2013  100
8         08/02/2013  102
9         22/02/2013  103
10        28/02/2013  103
...

I want to display count of tickets for each StateId for each month in a year, such as these :

StateID  FirstMonthCount  SecondMonthCount  Third...
100      2                2
101      2                0
102      1                1
103      0                2
...

I try to write a query but can't show result in each month

  SELECT StateID, Count(StateID) AS Count, From Ticket
  WHERE TicketDate >= '01/01/2013' AND TicketDate <= '31/01/2013' 
  Group By StateID Order By StateID

Upvotes: 1

Views: 1532

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You need to PIVOT your table like this:

SELECT
   StateID, [1] as FirstMonthCount, [2] as SecondMonthCount, ...
FROM
   (
      SELECT DATEPART(m, TicketDate) TheMonth, StateID 
      FROM tbl
      WHERE TicketDate >= '01/01/2013'
        AND TicketDate <= '31/12/2013'
   ) as src
PIVOT
   (
       COUNT(TheMonth)
       FOR TheMonth IN ([1], [2], ..., [12])
   ) as pvt

SQL FIDDLE DEMO

Upvotes: 1

Related Questions