Solved

SQL UNION ALL

Posted on 2013-12-13
13
375 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
  • 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: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
 
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: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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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: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:
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: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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
SQL Server 2008 R2 - Execution Plan 3 57
SqlAdvisor 2016 3 28
Dimension table indexes 8 13
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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 video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

896 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

15 Experts available now in Live!

Get 1:1 Help Now