I have a generic Sales table that I'm querying against and I want to return two aggregates of the table, but on two separate rows instead of one.
I have a query to get a value like this:
SELECT
SUM(o.SalesAmount) Value
,'Yearly Sales' Name
,'Yearly' Timeframe
FROM Orders o
Which returns a single row
Value | Name | Timeframe |
---|---|---|
1,000,000 | Yearly Sales | Yearly |
Is there a way for me to structure my query so I can return another aggregate on a second row, but with a different Name and Timeframe on the same query? Right now I'm duplicating the first query and a bunch of filters that I need the same in the second query, but changing the aggregate and the "Name" and then unioning them all together like this:
SELECT
SUM(o.SalesAmount) Value
,'Yearly Sales' Name
,'Yearly' Timeframe
FROM Orders o
WHERE
/* bunch of filters */
UNION ALL
SELECT
COUNT(o.Orders) Value
, 'Yearly Orders' Name
,'Yearly' Timeframe
FROM Orders o
WHERE
/* bunch of filters which are the same as the first */
I have about 10 other aggregations that I need to return in a similar manner and I don't think unioning 12 really similar queries is the way to go about it.
Ideally I'd want something like this (I understand this won't give me what I need) so I can use the same filters and not have to duplicate code.
SELECT
SUM(o.SalesAmount) Value
, COUNT(o.Orders) Value2
,'Yearly Sales' Name
, 'Yearly Orders' Name2
,'Yearly' Timeframe
FROM Orders o
WHERE
/* bunch of filters that I only have to manage in one spot */
That would then return this:
Value | Name | Timeframe |
---|---|---|
1,000,000 | Yearly Sales | Yearly |
100,000 | Yearly Orders | Yearly |
CodePudding user response:
You can unpivot it from a cte
with cte as (
SELECT
SUM(o.SalesAmount) Value1,
COUNT(o.Orders) Value2,
'Yearly' Timeframe
FROM Orders o
WHERE
/* bunch of filters */
)
SELECT Value1 Value,'Yearly Sales' Name, Timeframe
FROM cte
UNION ALL
SELECT Value2, 'Yearly Orders', Timeframe
FROM cte
CodePudding user response:
Use cross apply
to split each Orders
row into two:
select sum(split.Value) Value
,split.Name
,'Yearly' Timeframe
from Orders o
cross apply (values
('Yearly Sales' ,o.SalesAmount)
,('Yearly Orders',1)
) split(Name,Value)
group by split.Name
CodePudding user response:
You can unpivot it after aggregating in a derived table:
SELECT
v.Value
v.Name
t.Timeframe
FROM (
SELECT
SUM(o.SalesAmount) Sum,
COUNT(o.Orders) Count,
'Yearly' Timeframe
FROM Orders o
) o
CROSS APPLY (VALUES
('Yearly Sales', Sum),
('Yearly Orders', Count)
) v(Name, Value);