Solved

How can I combine the results of 2 queries in SQL

Posted on 2016-08-11
9
43 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
  • 5
  • 4
9 Comments
 
LVL 69

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 69

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
 
LVL 69

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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 69

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 69

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now