Solved

SQL UNION ALL

Posted on 2013-12-13
13
374 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:Wass_QA
  • 8
  • 4
13 Comments
 
LVL 45

Expert Comment

by:Kdo
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:Wass_QA
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:Wass_QA
ID: 39717876
I don't mean duplicates.
I mean 2 results for same service
thanks
0
 
LVL 45

Expert Comment

by:Kdo
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:Wass_QA
ID: 39717893
Sum active and Finalized.
thanks
0
 

Author Comment

by:Wass_QA
ID: 39718068
Any help is appreciated,
thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 45

Expert Comment

by:Kdo
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:Wass_QA
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:
Kdo 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:Wass_QA
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:Wass_QA
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:Wass_QA
ID: 39719847
Hello,
the code was missing         
) t0
GROUP BY [Service Type]

thanks
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

20 Experts available now in Live!

Get 1:1 Help Now