BIDeveloper - 1 year ago 74

SQL Question

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

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
```