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

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:

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);