Group by several columns with count on another column SQL Server

group by multiple columns
sql group by one column with multiple columns
group by multiple columns pandas
sql count group by multiple columns
group by having sql
count sql
sql aggregate functions
group by multiple columns mysql

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?

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

10.3 Grouping on Two or More Columns, How do I count different columns on the same table? a and b looks like a statement to MySQL (not actually sure if it would be a syntax error or if it would group on the 0 or 1 return -- I would assume a syntax error, especially for varchar fields). To group on multiple columns, you just use a comma. – Corbin Apr 30 '12 at 9:09 1

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

SQL GROUP BY: How To Apply It Effectively, (CASE WHEN StatusID = 4 THEN ClaimID END) FROM dbo. SQL COUNT () with group by and order by In this page, we are going to discuss the usage of GROUP BY and ORDER BY along with the SQL COUNT () function. The GROUP BY makes the result set in summary rows by the value of one or more columns. Each same value on the specific column will be treated as an individual group.

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

How to get count for different columns on same table, There is also a more modern syntax of achieving the same result, which So, a PIVOT query is an implicit GROUP BY query, essentially. because apparently CompanyName is the only column not explicitly Admittedly my experience is with MySQL mostly and I haven't spent much time on SQL Server. SQL Server COUNT() with GROUP BY clause example The following statement uses the COUNT(*) function to find the number of products in each product category: SELECT category_name, COUNT (*) product_count FROM production.products p INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY category_name ORDER BY product_count DESC ;

Group by several columns with count on another column SQL Server, This tutorial introduces you SQL GROUP BY that combines rows into groups and how to use SQL GROUP BY clause to group rows based on one or more columns. In other words, the WHERE clause is applied to rows whereas the HAVING column, and apply the COUNT function to each group as the following query:. 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 :

SQL GROUP BY, Group by several columns with count on another column SQL Server. sql group by one column with multiple columns sql group by multiple columns sql sum  Rolling up data from multiple rows into a single row may be necessary for concatenating data, reporting, exchanging data between systems and more. This can be accomplished by: The solution proposed in this tip explores two SQL Server commands that can help us achieve the expected results. The SQL Server T-SQL commands used are STUFF and FOR XML.

In SQL, how do I count DISTINCT over multiple columns?, SQL aggregate function like COUNT , AVG , and SUM have something in common: data into groups, which can be aggregated independently of one another. You can group by multiple columns, but you have to separate column names  Solution: There is a system view named "columns" in every database by which you can get the list of every kind of entities that exist in your database.You can only access this view by the schema called "information_schema" like information_schema.columns.

Comments
  • what is your dbms?
  • Please read the accepted answer of "Why not upload images of code on SO when asking a question?" and edit your question accordingly.
  • Thanks, that's great. Is there a way to tweak this though for group by date. Meaning, row 1 of query result is count of statuses for user 1 on the 12th. Row 2 is count of statuses for user2 on the 12th. Row 3 is count of statuses for user1 on the 13th etc..
  • @DelHabington, not sure that I understand what you mean... What are 12th rows, 13th rows and so on ?
  • Sorry for the confusion. When I say 12th and 13th, I'm referring to dates. So row 1 would be 12/12/2018, User1, and a count for all statuses. Row 2 would same date but for User2, and a count for all statuses. And repeat for each day. Look at the query result image I've uploaded to clarify
  • Ok @DelHabington, so you need an ORDER BY clause, by date then by user. I just updated my answer accordingly.
  • Make sure you upvote the answers that helped you figure it out