CODEWITHSUNDEEP
sqlsql-servergroup-bycountpivot
Del Habington
Del Habington

Reputation: 17

Group by several columns with count on another column SQL Server

I'm using SQL SERVER 2012 and I'm struggling with this SQL statement. Basically I have this table

table

I want to group by Date, and Username, with a count on the status column, like below :

query result

How can I achieve this?

Upvotes: 0

Views: 1814

Answers (3)

GMB
GMB

Reputation: 222492

You can use an aggredated query with a few conditional SUMs.

SELECT 
    LastUpdate,
    UpdatedBy as User, 
    SUM(CASE WHEN Status = 'A' THEN 1 ELSE 0 END) as A
    SUM(CASE WHEN Status = 'C' THEN 1 ELSE 0 END) as C
    SUM(CASE WHEN Status = 'D' THEN 1 ELSE 0 END) as D
    SUM(CASE WHEN Status = 'Z' THEN 1 ELSE 0 END) as Z
    SUM(CASE WHEN Status = 'X' THEN 1 ELSE 0 END) as X
FROM table
GROUP BY LastUpdate, UpdatedBy
ORDER BY LastUpdate, UpdatedBy

Upvotes: 2

Del Habington
Del Habington

Reputation: 17

Ok I figured it out with help from the guys answers

    SELECT 
    CAST(LastUpdate as DATE),
    UserName,
    SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) as [Status_1],
    SUM(CASE WHEN Status = 2 THEN 1 ELSE 0 END) as [Status_2],
    SUM(CASE WHEN Status = 3 THEN 1 ELSE 0 END) as [Status_3]
FROM Table
WHERE LastUpdate BETWEEN '2018-11-30 10:013:44.080' AND '2018-12-30 10:013:44.080'
    GROUP BY CAST(LastUpdate as DATE), UserName
    ORDER BY CAST(LastUpdate as DATE)

This is a sample query where I'm looking for records between two dates. The problem I was having was in part due to filtering on datetime rather than date. The lastupdate column is a datetime so by casting to date it solved the issue

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try using conditional aggregation

select LastUpdate,UpdatedBy, 
count(case when Status='A' then UpdatedBy end) as 'A',
count(case when Status='C' then UpdatedBy end) as 'C',
count(case when Status='D' then UpdatedBy end) as 'D',
count(case when Status='Z' then UpdatedBy end) as 'Z',
count(case when Status='X' then UpdatedBy end) as 'X'
from tablename
group by LastUpdate,UpdatedBy

Upvotes: 1

Related Questions