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
 General SQL Server Forums
 New to SQL Server Programming
 Way to group/count multiple unions together?

Author  Topic 

trollersteve
Starting Member

5 Posts

Posted - 2013-12-09 : 13:43:25
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-09 : 13:48:25
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-09 : 13:49:05
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 - 2013-12-09 : 13:54:18
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 - 2013-12-09 : 14:06:17
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
   

- Advertisement -