?
Solved

SQL UNION ALL

Posted on 2013-12-13
13
Medium Priority
?
385 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 46

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 46

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 46

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 46

Accepted Solution

by:
Kent Olsen earned 1200 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

850 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