CODEWITHSUNDEEP
mysql
Maris
Maris

Reputation: 131

MYSQL - additional analysis on sum(field) grouped by name

I have an orders table with the following fields : id, name, price_paid

The easiest part is this: 

SELECT 
    name, 
    SUM(price_paid) AS total_price_paid 
FROM 
    Orders GROUP BY 
name

How should I modify my SQL statement so I get the following at the output?

name, total_price_paid, purchase_level

Where purchase level would be:

Thank you in advance.

Upvotes: 1

Views: 24

Answers (2)

fancyPants
fancyPants

Reputation: 51878

SELECT 
    name, 
    SUM(price_paid) AS total_price_paid,
    CASE WHEN SUM(price_paid) BETWEEN 0 AND 100 THEN 1
         WHEN SUM(price_paid) BETWEEN 101 AND 350 THEN 2
         WHEN SUM(price_paid) > 350 THEN 3 END AS purchase_level
FROM 
    Orders 
GROUP BY 
    name

Upvotes: 1

Alma Do
Alma Do

Reputation: 37365

Use conditional sum:

SELECT 
  name, 
  SUM(IF(price_paid BETWEEN 0 AND 100, price_paid, 0)) AS sum_0_100,
  SUM(IF(price_paid BETWEEN 101 AND 350, price_paid, 0)) AS sum_101_350,
  SUM(IF(price_paid>350, price_paid, 0)) AS sum_350_plus
FROM 
  Orders 
GROUP BY 
  name

Or else, with level:

SELECT 
  name,
  SUM(price_paid),
  CASE
    WHEN price_paid BETWEEN 0 AND 100 THEN 1
    WHEN price_paid BETWEEN 101 AND 350 THEN 2
    WHEN price_paid>350 THEN 3
  END AS level
FROM 
  Orders 
GROUP BY 
  name,
  -- don't forget group then:
  level

Difference between those two queries are that fires will result in pivot while second will result in plain rows grouping.

Upvotes: 1

Related Questions