• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • Last Modified:

How can I combine the results of 2 queries in SQL

I have 2 queries below. Both will produce records in the below format. I would like to combine them, so that the "Closed" column between the 2 is added.

Closedate     CloseUser     Closed
8/1/2016         Bob                  7
8/1/2016         Shelly               9
8/2/2016         Bob                  5...........

The 2 queries are below. I would like to take the sum of both "Closed" columns and add them together. So in the example, If bob had 3 closed from the first query and 4 closed from the 2nd query, they would be added or grouped together to produce the first line.
Can someone assist? I tried using a union, but since I need to "group", I couldn't figure it out.

1st query:
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser
ORDER BY  CloseDate, TABLE1.CloseUser

2nd query:
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
ORDER BY  CloseDate, TABLE2.CloseUser

Open in new window

0
earwig75
Asked:
earwig75
  • 5
  • 4
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
and what do you need to do with the date? do you just need the user and the count?
0
 
Éric MoreauSenior .Net ConsultantCommented:
that would be:
select ClosePerson, count(Closed)
from (
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all 

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
) AS A
group by ClosePerson
ORDER BY  ClosePerson

Open in new window

0
 
earwig75Author Commented:
I need it to return 3 columns, like in my first example....

I made a small mistake, all dates are called "CloseDate" not LastUpdate

Date User Closed
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Éric MoreauSenior .Net ConsultantCommented:
so you need to also keep your date as is?

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser

ORDER BY  CloseDate, ClosePerson

Open in new window

0
 
earwig75Author Commented:
Yes, but I also need to group the Close column, right? I want it to give a sum for each day, for each person.
0
 
earwig75Author Commented:
it should be 1 row for each user, per date, with a count.
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>but I also need to group the Close column, right?

which column? the CloseD column? You don't need to group on it, you sum that column grouped by date and user.
0
 
Éric MoreauSenior .Net ConsultantCommented:
I have reread your question. I think your query should be:

select CloseDate, ClosePerson, sum(Closed)
from (
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all 

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
) AS A
group by CloseDate, ClosePerson
ORDER BY  CloseDate, ClosePerson

Open in new window

0
 
earwig75Author Commented:
Great, thank you. I was missing the select around my union, and your response solved that. Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now