Solved

SQL Union Showing Duplicates in Result Sets

Posted on 2011-03-14
10
334 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 51

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 51

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 51

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

Suggested Solutions

Title # Comments Views Activity
Copy data to New Year 9 32
How can I retrieve the column names from a T-SQL table? 2 16
What Is an Error? 2 24
SQL query 7 15
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

756 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