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!
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.
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]
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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?
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.
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 -- ?
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:
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
SELECT 'D', 1
UNION ALL
SELECT 'Z', 2
UNION ALL
SELECT 'A', 3
ORDER BY 1
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
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
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial