Link to home
Start Free TrialLog in
Avatar of Thomas_L_MTL
Thomas_L_MTL

asked on

SQL - Srting multiple merged queries

I'm trying to automate multiple reports into a single email/report that has multiple top 10 lists.  I'm able to merge the report and insert individual title lines. But I can't sort them properly. If I run the queries one at a time and sort/ ORDER By they come out properly. but the merged reports won't let me use a sort/Order by option.

The query below works but it shows the top 10 customers alphabetically not be descending value. if I add (ORDER BY sum (T1.[LineTotal]) desc) to the end I get errors. How can I get the 2 top 10 lists to sort differently.


SELECT cast (NULL as varchar),'Top 10 customers for 2015'

UNION all

SELECT top 10 sum (T1.[LineTotal]), T0.[CardName] FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] > '01/01/2015' GROUP BY T0.[CardName] 

union all

SELECT cast (NULL as varchar),'Top 10 customers for 2014'

UNION all

SELECT top 10 sum (T1.[LineTotal]), T0.[CardName] FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] between '01/01/2014' and '01/01/2015' GROUP BY T0.[CardName] 

Open in new window

Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

The ORDER BY in this cases exists outside the scope of the individual queries so reference it by the column name which you have omitted.  You can try using ordinal referencing by adding "ORDER BY 1" to order by the first column

SELECT cast (NULL as varchar),'Top 10 customers for 2015'

UNION all

SELECT top 10 sum (T1.[LineTotal]), T0.[CardName] FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] > '01/01/2015' GROUP BY T0.[CardName] 

union all

SELECT cast (NULL as varchar),'Top 10 customers for 2014'

UNION all

SELECT top 10 sum (T1.[LineTotal]), T0.[CardName] FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] between '01/01/2014' and '01/01/2015' GROUP BY T0.[CardName] 

ORDER BY 1

Open in new window

Curiosity overwhelms me ... would it work to have the 'various Top 10 reports' as subreports on a single main report, and email / Data Driven Subscription / whatever that report?
Avatar of Thomas_L_MTL
Thomas_L_MTL

ASKER

that helps, but it now sorts the 2 top 10 lists into a single tops 20 list descending. I'm hoping to have the following

Title
Top 10 list descending
Title 2
Top 10 list descending.
<air code> Add a sort_order column, throw everything in a subquery, then sort on sort_order

SELECT a.label, a.value
FROM (
   SELECT   'Top 10 customers for 2015' as label, NULL as value, 1 as sort_order
   UNION ALL
   SELECT top 10 sum (T1.[LineTotal]), T0.[CardName], 2
   FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >             '01/01/2015' GROUP BY T0.[CardName] 
   UNION ALL
   SELECT cast (NULL as varchar),'Top 10 customers for 2014', 3
   UNION ALL
   SELECT top 10 sum (T1.[LineTotal]), T0.[CardName], 4
   FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] between '01/01/2014' and '01/01/2015' GROUP BY T0.[CardName] 
-- you get the idea
) a
ORDER BY a.sort_order, a.label -- ?

Open in new window

Jim,

Although it is somewhat counter-intuitive for how ORDER BY typically works; it is applied outside the scope of the individual queries in the case of a UNION so wrapping them is unnecessary.  I just learned this myself not too long ago after years of doing precisely what you describe.

A quick demonstration that you can run yourself:

SELECT 'D' AS Label, 1 AS Value
UNION ALL
SELECT 'Z', 2
UNION ALL
SELECT 'A', 3
ORDER BY Label

Open in new window

or

SELECT 'D', 1
UNION ALL
SELECT 'Z', 2
UNION ALL
SELECT 'A', 3
ORDER BY 1

Open in new window

Its getting there. I now have the sorting right where is keeps the title in the right spots and orders the sales by descending value. But not it only shows the sales for the top 10 customers ALPHABETICALLY by descending sales value. So if our top sales is ZZZZ inc it will never show, because its not in the first 10 clients alphabetically.  

SELECT cast (NULL as varchar) as Sales,'Top 10 customers for 2015' as Title_or_Name,1 as Sort_Order

UNION all

SELECT top 10 sum (T1.[LineTotal]), T0.[CardName],2 FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] > '01/01/2015' GROUP BY T0.[CardName] 

union all

SELECT cast (NULL as varchar),'Top 10 customers for 2014',3

UNION all

SELECT top 10 sum (T1.[LineTotal]), T0.[CardName],4 FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] between '01/01/2014' and '01/01/2015' GROUP BY T0.[CardName] order by Sort_Order, Sales desc

Open in new window

In order for the TOP 10 to work you need to have an ORDER BY in the subqueries as well as the ORDER BY for the UNION as a whole.

SELECT CAST(NULL AS VARCHAR) AS Sales,'Top 10 customers for 2015' AS Title_or_Name, 1 AS Sort_Order

UNION ALL

(
	SELECT TOP 10 SUM(T1.[LineTotal]), T0.[CardName], 2
	FROM OINV T0
	INNER JOIN INV1 T1
		ON T0.[DocEntry] = T1.[DocEntry]
	WHERE T0.[DocDate] >= '20150101'
	GROUP BY T0.[CardName]
	ORDER BY 1 DESC
) AS Sales2015

UNION ALL

SELECT NULL, 'Top 10 customers for 2014', 3

UNION ALL

(
	SELECT top 10 SUM(T1.[LineTotal]), T0.[CardName], 4
	FROM OINV T0
	INNER JOIN INV1 T1
		ON T0.[DocEntry] = T1.[DocEntry]
	WHERE T0.[DocDate] >= '20140101'
		AND T0.DocDate < '20150101'
	GROUP BY T0.[CardName]
	ORDER BY 1 DESC
) AS Sales2014

ORDER BY Sort_Order, Sales desc

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial