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
 General SQL Server Forums
 New to SQL Server Programming
 Way to group/count multiple unions together?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

trollersteve
Starting Member

5 Posts

Posted - 12/09/2013 :  13:43:25  Show Profile  Reply with Quote
I have 4 archive tables and 1 active table that are created the same, but contain different data based on the date. I need to get results that have three columns: AuthorName, Month, Total. This is currently working, but through my research I can't find how to start going about dealing with the fact that each Author has some of his results from one month in one table and some in another table and how to add those together into one row.
Example:

(What I'm Getting)
AuthorName Month Total
Test, Fred 3 43
Test, Fred 3 12
Test, Fred 2 56
Test, Fred 5 35

(What I Want)
AuthorName Month Total
Test, Fred 3 55
Test, Fred 2 56
Test, Fred 5 35

------------------------------------

SELECT 
	AuthorName,
    Month(CreateDT) as Month, 
	COUNT(*) AS Total 
FROM [FM].[dbo].[WORKOBJ]

Where CreateDT >= '2013-01-01' and CreateDT <= '2013-12-31' 

GROUP BY 
	AuthorName,
   Month(CreateDT)

Union All

SELECT 
	AuthorName,
    Month(CreateDT) as Month, 
	COUNT(*) AS Total 
FROM [FM].[dbo].[Q1Tasks2013]

Where CreateDT >= '2013-01-01' and CreateDT <= '2013-12-31' 

GROUP BY 
	AuthorName,
   Month(CreateDT)

Union All

SELECT 
	AuthorName,
    Month(CreateDT) as Month, 
	COUNT(*) AS Total 
FROM [FM].[dbo].[Q2Tasks2013]

Where CreateDT >= '2013-01-01' and CreateDT <= '2013-12-31' 

GROUP BY 
	AuthorName,
   Month(CreateDT)

Union All

SELECT 
	AuthorName,
    Month(CreateDT) as Month, 
	COUNT(*) AS Total 
FROM [FM].[dbo].[Q3Tasks2013]

Where CreateDT >= '2013-01-01' and CreateDT <= '2013-12-31' 

GROUP BY 
	AuthorName,
   Month(CreateDT)

Union All

SELECT 
	AuthorName,
    Month(CreateDT) as Month, 
	COUNT(*) AS Total 
FROM [FM].[dbo].[Q4Tasks2013]

Where CreateDT >= '2013-01-01' and CreateDT <= '2013-12-31' 

GROUP BY 
	AuthorName,
   Month(CreateDT)

Order By
    AuthorName,
   Month(CreateDT)

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 12/09/2013 :  13:48:25  Show Profile  Reply with Quote
Just make your query a derived table:
SELECT
	AuthorName,
	[Month],
	SUM(Total) AS Total
FROM
	(
		-- Insert union query here
	) AS A
GROUP BY
	AuthorName,
	[Month]
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 12/09/2013 :  13:49:05  Show Profile  Reply with Quote
I'd also suggest you fix your schema and not have a different table for each quarter or date period or whatever. That is a bas design.
Go to Top of Page

trollersteve
Starting Member

5 Posts

Posted - 12/09/2013 :  13:54:18  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

I'd also suggest you fix your schema and not have a different table for each quarter or date period or whatever. That is a bas design.



I would love to, but it is a server/software package that was purchased before my time here.
Go to Top of Page

trollersteve
Starting Member

5 Posts

Posted - 12/09/2013 :  14:06:17  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Just make your query a derived table:
SELECT
	AuthorName,
	[Month],
	SUM(Total) AS Total
FROM
	(
		-- Insert union query here
	) AS A
GROUP BY
	AuthorName,
	[Month]




Thank you so much. That worked perfectly and was a lot more simple than some of the things I have been trying to do! I really appreciate the help.
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