Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Multiple Table Count/Sum Query

Author  Topic 

WebKill
Starting Member

32 Posts

Posted - 2013-06-07 : 15:20:18
Here is what I would like to do, I can get the data via two queries, but I would like it all side by side if possible.

I have two tables, one records how many records are uploaded with headers such as Date and Total, I use a query to Sum(Total) and then group by Date since there could be multiple uploads per day, this gives me a listing of Date and a count.

On the other table I have individual records that were uploaded, I can get the same count by doing a count(*) and ground by date.

I would like to combine this somehow so that I can see dates in which the counts do not match up.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-07 : 16:06:01
Join the two results sets (or rather the queries that generate those results sets) on the Date column. Be sure to use a FULL JOIN so you get data if only one result set has data for a particular date.
Go to Top of Page

WebKill
Starting Member

32 Posts

Posted - 2013-06-07 : 18:51:45
Alright, I gave that a try but I am getting weird NULL values when if I do them separately I get no nulls (there are no null values in either table)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 03:58:46
NULL values might be result of FULL JOIN which brings unmatched data from both the tables.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

WebKill
Starting Member

32 Posts

Posted - 2013-06-08 : 17:04:56
Is there a better way of doing this than joining two queries? Perhaps just identifying the offending Dates if count(table1.*) <> sum(table2.total) where table1.date = table2.date?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-08 : 19:07:34
There probably are better/different ways of writing the query; You can compare aggregates by adding a HAVING clause. If you post some sample data and the DDL for the tables in a consumable format, you would most certainly get better and more precise responses. Take a look at this article to see how to get DDL: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

WebKill
Starting Member

32 Posts

Posted - 2013-06-10 : 11:06:29
[code]CREATE TABLE dbo.Uploads (UploadDate date, TotalItems int)
CREATE TABLE dbo.Records (RecordDate date, IDNumber int)

INSERT INTO dbo.Uploads VALUES ('2013-06-09', '2'), ('2013-06-09', '3'), ('2013-06-10', '8')
INSERT INTO dbo.Records VALUES ('2013-06-09', '1'), ('2013-06-09', '2'), ('2013-06-09', '3'), ('2013-06-09', '4'), ('2013-06-09', '5')
INSERT INTO dbo.Records VALUES ('2013-06-10', '6'), ('2013-06-10', '7'), ('2013-06-10', '3'), ('2013-06-10', '8'), ('2013-06-10', '9'), ('2013-06-10', '10')[/code]


Here is what I have tried so far:

[code]Select a.Date, a.[Record Count], b.[Upload Count] from
(Select RecordDate as [Date], COUNT(*) as [Record Count] FROM dbo.Records group by RecordDate) a full join
(Select UploadDate) as [Date], SUM(TotalItems) as [Upload Count] FROM dbo.Uploads group by UploadDate) b on a.Date = b.Date[/code]

This will work, but if I try to identify the mismatched values by adding "and a.[Record Count] <> b.[Upload Count]" then it gives me a null date value.

Like I said before, if there is a better way then I am all for it.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-10 : 11:46:27
See if either of the two queries gives you what you are looking for?
Select a.Date, a.[Record Count], b.[Upload Count],
CASE WHEN ISNULL(a.[Record Count],-1)<> ISNULL(b.[Upload Count],-2) THEN 'Different' ELSE 'Same' END AS Status from
(Select RecordDate as [Date], COUNT(*) as [Record Count] FROM dbo.Records group by RecordDate) a full join
(Select UploadDate as [Date], SUM(TotalItems) as [Upload Count] FROM dbo.Uploads group by UploadDate) b on a.Date = b.Date

Select a.Date, a.[Record Count], b.[Upload Count] from
(Select RecordDate as [Date], COUNT(*) as [Record Count] FROM dbo.Records group by RecordDate) a full join
(Select UploadDate as [Date], SUM(TotalItems) as [Upload Count] FROM dbo.Uploads group by UploadDate) b on a.Date = b.Date
WHERE ISNULL(a.[Record Count],-1)<> ISNULL(b.[Upload Count],-2)
Go to Top of Page

WebKill
Starting Member

32 Posts

Posted - 2013-06-11 : 10:16:28
Thanks James, when I took a look at your second query, it occurred to me what I was doing wrong; I was putting and before record count <> upload count instead of when, it's working perfectly now!
Go to Top of Page
   

- Advertisement -