SQL -  Srting multiple merged queries

Thomas_L_MTL
Thomas_L_MTL used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005

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

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

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

Author

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

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

Brian CroweDatabase Administrator
Top Expert 2005

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

Author

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

Brian CroweDatabase Administrator
Top Expert 2005

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

Information Technology Specialist
Commented:
You might think about using an analytical query, something like -
select customer, Year, YearlySum from
(select customer, Year, YearlySum,
 row_number() over (partition by customer, year order by YearlySum desc) rn from
 (select customer, datepart(year,DocDate) as Year,
  sum(LineTotal) as YearlySum
  from OINV O inner join INV1 I on O.DocEntry = I.DocEntry
  group by customer, datepart(year,DocDate)
 ) x
) y
where y.rn <= 10;
Providing some sample data and expected results might assist the experts in developing a more exact solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial