Solved

SQL Union Showing Duplicates in Result Sets

Posted on 2011-03-14
10
335 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
[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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

752 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