Solved

SQL UNION ALL

Posted on 2013-12-13
13
378 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
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: 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: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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

772 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