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.
| 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. |
 |
|
|
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) |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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? |
|
|
|
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 |
|
|
|
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. |
|
|
|
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.DateSelect 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.DateWHERE ISNULL(a.[Record Count],-1)<> ISNULL(b.[Upload Count],-2) |
|
|
|
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! |
|
|
|
|
|
|
|
|
Multiple Table Count/Sum Query