Solved

How can I combine the results of 2 queries in SQL

Posted on 2016-08-11
9
59 Views
Last Modified: 2016-08-11
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
Comment
Question by:earwig75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41752112
and what do you need to do with the date? do you just need the user and the count?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41752114
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
 

Author Comment

by:earwig75
ID: 41752156
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41752183
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
 

Author Comment

by:earwig75
ID: 41752194
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
 

Author Comment

by:earwig75
ID: 41752201
it should be 1 row for each user, per date, with a count.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41752204
>>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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41752214
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
 

Author Closing Comment

by:earwig75
ID: 41752397
Great, thank you. I was missing the select around my union, and your response solved that. Thanks again!
0

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question