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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WebKill
Starting Member

32 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

3873 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

32 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
52326 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

32 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

3873 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

32 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

3873 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

32 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  
 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.12 seconds. Powered By: Snitz Forums 2000