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

Thomas_L_MTLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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 DudeCommented:
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?
Thomas_L_MTLAuthor 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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Jim HornSQL Server Data DudeCommented:
<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 AdministratorCommented:
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

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

awking00Information Technology SpecialistCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.