CODEWITHSUNDEEP
sqlsql-serversql-server-2008
linker85
linker85

Reputation: 1651

How should my query be?

I have a table like this:

    date,               flag
    22/05/13             1
    22/05/13             1
    22/05/13             0
    23/05/13             1
    23/05/13             0

So I need a query where I count in different columns the 2 possible values of flag.

    date       flag1        flag0
    22/05/13    2            1
    23/05/13    1            1

How should I write my query in order to get the data in the way I showed above?

Upvotes: 3

Views: 78

Answers (3)

Taryn
Taryn

Reputation: 247860

You could use the PIVOT function to get the result:

select date, [1] as flag1, [0] as flag0
from yt
pivot
(
  count(flag)
  for flag in ([1], [0])
) piv;

See SQL Fiddle with Demo

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

SELECT [date], sum(flag) "flag1", sum(1-flag) "flag0"
FROM [table]
GROUP BY [date]

Normally I'd use a case statement inside the SUM() functions, but in this case it works out that we can get away with simple (and faster) expressions.

Upvotes: 5

Arion
Arion

Reputation: 31249

Something like this:

SELECT
    [date]
    SUM(CASE WHEN tbl.flag=0 THEN 1 ELSE 0 END) AS flag0,
    SUM(CASE WHEN tbl.flag=1 THEN 1 ELSE 0 END) AS flag1
FROM
    tbl
GROUP BY
    tbl.[date]

Upvotes: 5

Related Questions