Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Union Showing Duplicates in Result Sets

Posted on 2011-03-14
10
Medium Priority
?
338 Views
Last Modified: 2012-06-21
I have a query that is looking at and "open" and "history" table and combining the query using a UNION.  I am getting the results in each individual result set that I would have expected, but it is showing duplicates in the result sets versus combining the result duplicates into one row.  

Any ideas?  SQL Query and Results below
DECLARE @doc_date DATETIME

SET @doc_date = '1/1/2011'

SELECT  created_by,
		[Week]=DATEPART(ww,doc_date),
        [Orders_Entered] = COUNT(*)
FROM    dbo.tspvSalesDocumentHistory
WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num=''
GROUP BY Created_By, DATEPART(ww,doc_date)
UNION
SELECT  created_by,
		[Week]=DATEPART(ww,doc_date),
        [Orders_Entered] = COUNT(*)
FROM    dbo.tspvSalesDocument
WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num=''
GROUP BY Created_By, DATEPART(ww,doc_date)
ORDER BY DATEPART(ww,Doc_Date), Created_By desc


===============================================
                   Results
===============================================
created_by	Week	Orders_Entered
mblack         	11	9
mblack         	11	19
cmoore         	11	24
cmoore         	11	8
cbigbee        	11	5
cbigbee        	11	6
bwalker        	11	59
bwalker        	11	14
bsatterfield1  	11	7
bsatterfield1  	11	4

Open in new window

0
Comment
Question by:r270ba
  • 4
  • 3
  • 3
10 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35131771
try something like this...

union first,  then count

select created_by,
            [Week]=DATEPART(ww,doc_date),
        [Orders_Entered] = COUNT(*)
            from (
SELECT  created_by,
            doc_date
FROM    dbo.tspvSalesDocumentHistory
WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num=''
UNION
SELECT  created_by,
        doc_date
FROM    dbo.tspvSalesDocument
WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num='') as combined
GROUP BY Created_By, DATEPART(ww,doc_date)
ORDER BY DATEPART(ww,Doc_Date), Created_By desc

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35131779
you might want to change to UNION ALL instead of UNION if you want the days from each part of the query to be counted individually
0
 

Author Comment

by:r270ba
ID: 35131881
Msg 8120, Level 16, State 1, Line 5
Column 'dbo.tspvSalesDocumentHistory.Doc_Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 61

Expert Comment

by:HainKurt
ID: 35131898
what do you expect?

give sample for first part
give sample for second part

and tell us what do you expect

0
 

Author Comment

by:r270ba
ID: 35131912
I expect the results to not have the names listed twice.  For example, the first two rows of the results show:

created_by      Week      Orders_Entered
mblack               11      9
mblack               11      19

I want that to show:

created_by      Week      Orders_Entered
mblack               11      28
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35131924
try this:

select created_by, Week, sum(Orders_Entered) from (
your first query here
UNION ALL
your second query here
) x group by created_by, Week

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35131937
ie

select created_by, Week, sum(Orders_Entered) Orders_Entered from (
your original query here with "UNION ALL"
) x group by created_by, Week
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 35131954
SELECT  created_by,
            [Week]=DATEPART(ww,doc_date),
        [Orders_Entered] = COUNT(*)
FROM    (select * from dbo.tspvSalesDocumentHistory
union all
select * dbo.tspvSalesDocument) as combined

WHERE   Doc_Date >= @doc_date
        AND Sales_Doc_Type = 'order'
        AND Original_Num=''
GROUP BY Created_By, DATEPART(ww,doc_date)
ORDER BY DATEPART(ww,Doc_Date), Created_By desc
0
 

Author Comment

by:r270ba
ID: 35132015
That was close.  Here is the final query:


DECLARE @doc_date DATETIME

SET @doc_date = '1/1/2011'

SELECT  created_by,
        [Week] = DATEPART(ww, doc_date),
        [Orders_Entered] = COUNT(*)
FROM    ( select    Created_By,
                    Doc_Date
          from      tspvSalesDocumentHistory
          WHERE     Doc_Date >= @doc_date
                    AND Sales_Doc_Type = 'order'
                    AND Original_Num = ''
          union all
          select    Created_By,
                    Doc_Date
          FROM      tspvSalesDocument
          WHERE     Doc_Date >= @doc_date
                    AND Sales_Doc_Type = 'order'
                    AND Original_Num = ''
        ) as combined
WHERE   DATEPART(ww, combined.Doc_Date) = DATEPART(ww, GETDATE())
GROUP BY Created_By,
        DATEPART(ww, doc_date)
ORDER BY DATEPART(ww, Doc_Date),
        Created_By

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35132070
the outer criteria should be able to be "pushed" from the outside to the inner, but yes functionally that should be the same
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

783 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