[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Transact SQL to get multiple counts from multiple rows into one row with counts...

I have a small sample of one of our tables:
User   Category  InitialDate     LastRequestDate    LastUpdate
1         1907         1/1/2010      2/1/2011                 1/15/2011
2         6509         6/10/2010    4/1/2011                 3/15/2011
3         3102         3/1/2010      3/15/2011               2/17/2011
4         3102         3/1/2010      2/15/2011               3/17/2011
5         6509         5/10/2010    2/1/2011                 1/25/2011
6         1907         1/1/2010      3/1/2011                 4/15/2011

Each user has one row in the table and users are constantly being added.  
There are currently about 30 categories but new categories are always being added.

I need to do a summary table for each day for each category what are the counts...
Do you have a suggestion for the best way to handle this besides doing temporary tables, cursors, etc...?  I'm thinking something with row_number partition or / and cte...but not sure how to do this...

Date             Catalog    InitialCount    LastRequestCount   LastUpdateCount
1/1/2010       1907        2                    0                             0      
3/1/2010       3102        2                    0                             0
5/10/2010     6509        1                    0                             0
6/10/2010     6509        1                    0                             0
1/15/2011     1907        0                    0                             2
1/25/2011     6509        0                    0                             1
2/1/2011       1907        0                    1                             0
2/1/2011       6509        0                    1                             0
2/15/201       3102        0                    1                             0
2/17/2011     3102        0                    0                             1
3/1/2011       1907        0                    1                             0
3/15/2011     3102        0                    1                             0
3/17/2011     3102        0                    0                             1
4/1/2011       6509        0                    1                             0
4/15/2011     1907        0                    0                             1

Thanks in advance!
0
Bodhi108
Asked:
Bodhi108
1 Solution
 
radcaesarCommented:
do a count and group it by that date
0
 
Bodhi108Author Commented:
A count and group by the date, category won't work.  The dates are different and the counts will get duplicated.  I originally tried this.
0
 
Anthony PerkinsCommented:
I am confused.  The following two statements are contradictory:
I need to do a summary table for each day for each category what are the counts...
...
A count and group by the date, category won't work.  The dates are different and the counts will get duplicated.  


So what is it you really want?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Ron MalmsteadInformation Services ManagerCommented:
SELECT SUM(InitialCount) as Icount, Sum(LastRequestCount) as LRcount, Sum(LastUpdateCount) as LUcount FROM(SELECT * FROM Table1
Left Join Table2 on Table1.Category = Table2.Catalog) as tmptable1
GROUP BY Table1.Category


Try that. Replace table1 with the name of your first table above, and table2 as the second.
0
 
Ron MalmsteadInformation Services ManagerCommented:
SELECT Table1.Category,SUM(InitialCount) as Icount, Sum(LastRequestCount) as LRcount, Sum(LastUpdateCount) as LUcount FROM(SELECT * FROM Table1
Left Join Table2 on Table1.Category = Table2.Catalog) as tmptable1
GROUP BY Table1.Category



0
 
Bodhi108Author Commented:
The above will get me everything except for the individual dates...

First, I won't get the first column with dates...  The sum won't work because these are date columns so it has to be count(InitialCount)  as LastInitialCount, count(LastRequestDate) as LastRequestCount.  The dates are the issue.  InitialCountDate and LastRequestDate do not have all the dates so I can't do a join.  Another words Initial Date may have 1/1/2011 and LastRequestDate may not have 1/1/2011 and visa versa...  I think I need to do something with Row_Number and partition...

Date             Catalog    InitialCount    LastRequestCount   LastUpdateCount
1/1/2010       1907        2                    0                             0      
3/1/2010       3102        2                    0                             0
5/10/2010     6509        1                    0                             0
6/10/2010     6509        1                    0                             0
1/15/2011     1907        0                    0                             2
1/25/2011     6509        0                    0                             1
2/1/2011       1907        0                    1                             0
2/1/2011       6509        0                    1                             0
2/15/201       3102        0                    1                             0
2/17/2011     3102        0                    0                             1
3/1/2011       1907        0                    1                             0
3/15/2011     3102        0                    1                             0
3/17/2011     3102        0                    0                             1
4/1/2011       6509        0                    1                             0
4/15/2011     1907        0                    0                             1
0
 
jonaskaCommented:
The fastest way to do this I came up with is this:
SELECT [Date], Category AS [Catalog], SUM(InitialCount) AS InitialCount, SUM(LastRequestCount) AS LastRequestCount, SUM(LastUpdateCount) AS LastUpdateCount
FROM   (SELECT InitialDate AS [Date], Category, COUNT(*) AS InitialCount, 0 AS LastRequestCount, 0 AS LastUpdateCount FROM UserTable GROUP BY InitialDate, Category
		UNION
		SELECT LastRequestDate, Category, 0, COUNT(*), 0 FROM UserTable GROUP BY LastRequestDate, Category
		UNION
		SELECT LastUpdate, Category, 0, 0, COUNT(*) AS Cnt FROM UserTable GROUP BY LastUpdate, Category) AS dates
GROUP BY [Date], Category

Open in new window


I'm not sure if it is the most performant way, althoug cursors can be time saving some times...
0
 
Bodhi108Author Commented:
Excellent answer!  Very impressed!  Thanks!  Getting my money's worth!
0
 
Ron MalmsteadInformation Services ManagerCommented:
Clearly I misunderstood the question. lol.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now