SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Multiple Table Count/Sum Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WebKill
Starting Member

29 Posts

Posted - 06/07/2013 :  15:20:18  Show Profile  Reply with Quote
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.

Edited by - WebKill on 06/08/2013 17:02:59

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/07/2013 :  16:06:01  Show Profile  Reply with Quote
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

29 Posts

Posted - 06/07/2013 :  18:51:45  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/08/2013 :  03:58:46  Show Profile  Reply with Quote
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

29 Posts

Posted - 06/08/2013 :  17:04:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/08/2013 :  19:07:34  Show Profile  Reply with Quote
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

29 Posts

Posted - 06/10/2013 :  11:06:29  Show Profile  Reply with Quote
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')



Here is what I have tried so far:

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


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.

Edited by - WebKill on 06/11/2013 10:12:05
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/10/2013 :  11:46:27  Show Profile  Reply with Quote
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

29 Posts

Posted - 06/11/2013 :  10:16:28  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000