Solved

SQL UNION ALL

Posted on 2013-12-13
13
380 Views
Last Modified: 2013-12-15
Hello,
can you please help, I need to combine below into one, (without the union all).

if I run below code, I get duplicates
example
Service1        1      25.62      0.00      31.12      1.56      35.78
Service1            645      6009.66      855.66      7437.85      372.36      8552.9443
Service 2      3      14.70      0.00      14.70      0.74      16.91
Service 2      3721      22801.99      3357.51      30552.37      1536.22      35137.3031
Service 3      2      71.80      10.90      88.70      4.44      101.98
Service 3      409      13918.89      2307.92      19288.86      1229.42      22054.11

Any help is appreciated.

Select st.Description as [Service Type] ,Count (ST.ServiceTypeID) AS [Count], SUM (BaseAmount) AS [BaseAmount],
SUM (FuelAmount) AS [FuelAmount],SUM (SubTotalAmount) AS [SubTotalAmount],SUM (Tax1Amount) AS [Tax1Amount],
SUM (TotalAmount) AS [TotalAmount]
FROM ActiveOrders Ao
LEFT JOIN Clients on Ao.accountnumber = clients.accountnumber
LEFT JOIN servicetypes st ON Ao.ServiceTypeId = st.ServiceTypeID
WHERE  Ao.servicetypeid = st.servicetypeid
AND AO.AccountNumber = 451 and OrderDate >= '2013-01-01'
Group by st.Description,st.servicetypeid  

UNION ALL

Select st.Description as [Service Type] ,Count (ST.ServiceTypeID) AS [Count], SUM (BaseAmount) AS [BaseAmount],
SUM (FuelAmount) AS [FuelAmount],SUM (SubTotalAmount) AS [SubTotalAmount],SUM (Tax1Amount) AS [Tax1Amount],
SUM (TotalAmount) AS [TotalAmount]
FROM finalizedorders fo
LEFT JOIN Clients on fo.accountnumber = clients.accountnumber
LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID
WHERE  fo.servicetypeid = st.servicetypeid  
AND FO.AccountNumber = 451 and OrderDate >= '2013-01-01'
Group by st.Description,st.servicetypeid  
Order By st.Description
0
Comment
Question by:W.E.B
[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
  • 8
  • 4
13 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39717837
Hi Wass,

I don't see duplicates in your example.  In this context, a duplicate occurs when all columns match.

Second, UNION ALL preserves the duplicates.  It merely merges all of the rows into a single result set.

If you want to filter the duplicates (based on all columns) use UNION instead of UNION ALL.



Good Luck,
Kent
0
 

Author Comment

by:W.E.B
ID: 39717874
Hello,
same thing, if I try Union,

I get  same results
Service1
Service1
Service 2
Service 2
Service 3
Service 3
thanks
0
 

Author Comment

by:W.E.B
ID: 39717876
I don't mean duplicates.
I mean 2 results for same service
thanks
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39717885
Ah.  Ok.

That's what UNION / UNION ALL does.  Just merges with/without filtering the duplicates.

Do you want just the active orders, just the finalized orders, the sum of the active and finalized, the finalized when known otherwise the active, etc...?


Kent
0
 

Author Comment

by:W.E.B
ID: 39717893
Sum active and Finalized.
thanks
0
 

Author Comment

by:W.E.B
ID: 39718068
Any help is appreciated,
thanks
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39718202
You can wrap that with an outer query that sums the data.

SELECT [Service Type], Sum[Count] as [Count], Sum([BaseAmount] as [BaseAmount],
  SUM ([FuelAmount]) AS [FuelAmount],SUM ([SubTotalAmount]) AS [SubTotalAmount],SUM ([Tax1Amount]) AS [Tax1Amount],
	SUM ([TotalAmount]) AS [TotalAmount]
FROM
(
	SELECT st.Description as [Service Type] ,Count (ST.ServiceTypeID) AS [Count], SUM (BaseAmount) AS [BaseAmount],
	SUM (FuelAmount) AS [FuelAmount],SUM (SubTotalAmount) AS [SubTotalAmount],SUM (Tax1Amount) AS [Tax1Amount],
	SUM (TotalAmount) AS [TotalAmount]
	FROM ActiveOrders Ao
	LEFT JOIN Clients on Ao.accountnumber = clients.accountnumber
	LEFT JOIN servicetypes st ON Ao.ServiceTypeId = st.ServiceTypeID
	WHERE  Ao.servicetypeid = st.servicetypeid
	AND AO.AccountNumber = 451 and OrderDate >= '2013-01-01'
	GROUP BY st.Description,st.servicetypeid  

	UNION ALL

	SELECT st.Description as [Service Type] ,Count (ST.ServiceTypeID) AS [Count], SUM (BaseAmount) AS [BaseAmount],
	SUM (FuelAmount) AS [FuelAmount],SUM (SubTotalAmount) AS [SubTotalAmount],SUM (Tax1Amount) AS [Tax1Amount],
	SUM (TotalAmount) AS [TotalAmount]
	FROM finalizedorders fo
	LEFT JOIN Clients on fo.accountnumber = clients.accountnumber
	LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID
	WHERE  fo.servicetypeid = st.servicetypeid  
	AND FO.AccountNumber = 451 and OrderDate >= '2013-01-01'
	GROUP BY st.Description,st.servicetypeid  
)
ORDER BY [Service Type]

Open in new window


You can also select just the rows in the inner sub-queries (without summing) and let the outer query sum the values.  If you've got a LOT of data you may get better performance from the query above.


Kent
0
 

Author Comment

by:W.E.B
ID: 39718666
Hello,
Error expectin \ or select.

if I run the inner sub-querry, I get 2 results (because of 2 tables., the Union / union all didn't combine),

I get  same results
Service1
Service1
Service 2
Service 2
Service 3
Service 3

I need to have the results as
Service1
Service 2
Service 3

I think I need to do the query this way, but I can't figure our the join part

Select st.Description as [Service Type] ,Count (ST.ServiceTypeID) AS [Count], SUM (BaseAmount) AS [BaseAmount],
SUM (FuelAmount) AS [FuelAmount],SUM (SubTotalAmount) AS [SubTotalAmount],SUM (Tax1Amount) AS [Tax1Amount],
SUM (TotalAmount) AS [TotalAmount]
FROM ActiveOrders Ao , finalizedorders fo
LEFT JOIN Clients on Ao.accountnumber = clients.accountnumber
LEFT JOIN servicetypes st ON Ao.ServiceTypeId = st.ServiceTypeID
WHERE  Ao.servicetypeid = st.servicetypeid
AND AO.AccountNumber = 451 and OrderDate >= '2013-01-01'
Group by st.Description,st.servicetypeid  

thanks.
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 300 total points
ID: 39718675
How many rows are in the ActiveOrders and FinalizedOrders tables?  Do they have the same structure?

I left the alias off of that query.  Try this one:

SELECT [Service Type], Sum[Count] as [Count], Sum([BaseAmount] as [BaseAmount],
  SUM ([FuelAmount]) AS [FuelAmount],SUM ([SubTotalAmount]) AS [SubTotalAmount],SUM ([Tax1Amount]) AS [Tax1Amount],
	SUM ([TotalAmount]) AS [TotalAmount]
FROM
(
	SELECT st.Description as [Service Type], Count (ST.ServiceTypeID) AS [Count], SUM (BaseAmount) AS [BaseAmount],
	  SUM (FuelAmount) AS [FuelAmount], SUM (SubTotalAmount) AS [SubTotalAmount], SUM (Tax1Amount) AS [Tax1Amount],
	  SUM (TotalAmount) AS [TotalAmount]
	FROM ActiveOrders Ao
	LEFT JOIN Clients on Ao.accountnumber = clients.accountnumber
	LEFT JOIN servicetypes st ON Ao.ServiceTypeId = st.ServiceTypeID
	WHERE  Ao.servicetypeid = st.servicetypeid
	AND AO.AccountNumber = 451 and OrderDate >= '2013-01-01'
	GROUP BY st.Description,st.servicetypeid  

	UNION ALL

	SELECT st.Description as [Service Type] ,Count (ST.ServiceTypeID) AS [Count], SUM (BaseAmount) AS [BaseAmount],
	  SUM (FuelAmount) AS [FuelAmount], SUM (SubTotalAmount) AS [SubTotalAmount], SUM (Tax1Amount) AS [Tax1Amount],
	  SUM (TotalAmount) AS [TotalAmount]
	FROM finalizedorders fo
	LEFT JOIN Clients on fo.accountnumber = clients.accountnumber
	LEFT JOIN servicetypes st ON fo.ServiceTypeId = st.ServiceTypeID
	WHERE  fo.servicetypeid = st.servicetypeid  
	AND FO.AccountNumber = 451 and OrderDate >= '2013-01-01'
	GROUP BY st.Description,st.servicetypeid  
) t0
ORDER BY [Service Type]

Open in new window

0
 

Author Comment

by:W.E.B
ID: 39718686
hello,
error

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near 't0'.
0
 

Author Comment

by:W.E.B
ID: 39719204
Help is appreciated.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39719427
The solution provided is just missing some parenthesis, that's all:
SELECT [Service Type], SUM([Count]) as [Count], Sum([BaseAmount]) as [BaseAmount],
0
 

Author Closing Comment

by:W.E.B
ID: 39719847
Hello,
the code was missing         
) t0
GROUP BY [Service Type]

thanks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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