BIDeveloper BIDeveloper - 7 months ago 13
SQL Question

T-SQL List of Results plus one row with a summary of less important values

Table is populated as follows

CustomerName SalesValue
CusA 100
CusB 250
CusC 900
CusD 1200
CusE 2500


I want a query which will list sales values for all of my customers if the value (per customer) is over 1000. If it's less, then I just want to see one summary row indicating total value of all customers <= 1000. Results would look something like this:

CusE 2500
CusD 1200

(Small Value Customers) 1250

Total 4950


Thanks in advance.

Answer

Perhaps:

WITH sales AS
(
    SELECT [CustomerName], [SalesValue],
       CustSales = SUM(SalesValue)OVER(PARTITION BY CustomerName)
    FROM dbo.Sales
)
SELECT CustomerName, CustSales FROM(
  SELECT 1 AS Source, 
       CustomerName, 
       CustSales = SUM(CustSales) FROM sales 
  GROUP BY  CustomerName
  HAVING SUM(CustSales) > 1000

  UNION ALL

  SELECT 2 As Source, 
       CustomerName = '(Small Value Customers)', 
       CustSales = SUM(CustSales) FROM sales 
  WHERE CustSales <= 1000

  UNION ALL

  SELECT 3 As Source, 
       CustomerName = 'Total', 
       CustSales = SUM(CustSales) FROM sales 
) AS X
ORDER BY X.Source, X.CustSales DESC

DEMO