CODEWITHSUNDEEP
sqlsql-serveradventureworks
alqueen
alqueen

Reputation: 233

List of people who meet specific conditions

I need a list of customers who bought products in a given year for a total amount of more than 80% of the largest total amount in a given year. I have a list of all persons and their total amount in each year, but I don't know how can I filter it. I tried to use MAX function and use the highest SUM but it doesn't work or I do it wrong. My current query is:

SELECT (p.LastName + ' ' + p.FirstName) AS 'Customer', s.CustomerID AS 'CustomerId',    YEAR(s.OrderDate) AS Rok, SUM(s.SubTotal) AS 'Amount', COUNT(*) AS 'number of purchases'

FROM Sales.SalesOrderHeader s JOIN Sales.Customer c ON s.CustomerID = c.CustomerID
JOIN Person.Person p ON c.PersonID = p.BusinessEntityID

GROUP BY s.CustomerID,  YEAR(s.OrderDate), p.LastName, p.FirstName

ORDER BY  s.CustomerID, YEAR(s.OrderDate);

Upvotes: 0

Views: 73

Answers (2)

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

If I understand your question correctly it should be okay:

with cte as (
SELECT 
    (p.LastName + ' ' + p.FirstName) AS 'Customer', s.CustomerID AS 'CustomerId',    YEAR(s.OrderDate) AS Rok, SUM(s.SubTotal) AS 'Amount', COUNT(*) AS 'number of purchases'
FROM Sales.SalesOrderHeader s JOIN Sales.Customer c ON s.CustomerID = c.CustomerID
JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
GROUP BY s.CustomerID,  YEAR(s.OrderDate), p.LastName, p.FirstName
), Max80Percent as 
(
    select 
         *
        ,0.8 * MAX(Amount) OVER (PARTITION BY Rok) as Threshold
    from cte
)
select
    *
from Max80Percent
where Amount > Threshold

Upvotes: 1

AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

You can use a sub query to filter 

SELECT * FROM (SELECT (p.LastName + ' ' + p.FirstName) AS 'Customer',
s.CustomerID AS 'CustomerId',    YEAR(s.OrderDate) AS Rok, 
SUM(s.SubTotal) AS 'Amount', COUNT(*) AS 'number of purchases'
FROM Sales.SalesOrderHeader s JOIN Sales.Customer c 
ON s.CustomerID = c.CustomerID
JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
GROUP BY s.CustomerID,  YEAR(s.OrderDate), p.LastName, p.FirstName
ORDER BY  s.CustomerID, YEAR(s.OrderDate)) z 
WHERE z.Amount>80;

The logic you need to implement can written in where clause this is a sample filter that I have used

Upvotes: 0

Related Questions