How to use group by function for more columns-CodePudding

I have used following query to get below output from my exisiting database.

select date(RaisedTime) as date, object,User,Count(*) as total from table1 where object like '%Object%' and User in ('User1','User2','User3','User4','User5','User6') group by date(RaisedTime),Object,User;

The result is what I needed but not the way I need it. I need to show this with much analyzed way such as below,

Can someone help me to do what I need?

CodePudding user response：

SELECT DATE(RaisedTime) AS `date`, 
       Object,
       SUM(User = 'User1') AS User1,
-- ...
       SUM(User = 'User6') AS User6
FROM table1 
WHERE Object LIKE '%Object%' 
  AND User IN ('User1','User2','User3','User4','User5','User6') 
GROUP BY DATE(RaisedTime), Object;

CodePudding user response：

select `date`,users,revenue, max(case when seq = 1 then object end) objA, max(case when seq = 2 then object end) objB, max(case when seq = 3 then object end) objC from (select `date`, object, users,revenue, row_number() over(partition by `date` order by `date`) seqfrom UserAnalysis ) d group by `date`;

You can use row_number() to analyze the result in a proper way. Please refer to this image for the result output. SQL Result