BIDeveloper BIDeveloper - 2 years ago 81
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 Source


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


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


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


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download