CODEWITHSUNDEEP
mysql
Khaled Lela
Khaled Lela

Reputation: 8119

How to group result retrieved from one column in mysql

I would like do some thing like this:

SELECT sum(quantity) 
FROM orders
WHERE order_code between **code1** and **code2** as group1 
and order_code between **code3** and **code4** as group2
and order_code between **code5** and **code6** as group3

Upvotes: 0

Views: 100

Answers (1)

ruakh
ruakh

Reputation: 183321

If you want one row with three columns:

SELECT SUM(IF(order_code BETWEEN ... AND ..., quantity, 0)), -- group1
       SUM(IF(order_code BETWEEN ... AND ..., quantity, 0)), -- group2
       SUM(IF(order_code BETWEEN ... AND ..., quantity, 0))  -- group3
  FROM orders
;

If you want one column with three rows:

SELECT group_number,
       SUM(quantity)
  FROM ( SELECT quantity,
                CASE WHEN order_code BETWEEN ... AND ... THEN 1 -- group1
                     WHEN order_code BETWEEN ... AND ... THEN 2 -- group2
                     WHEN order_code BETWEEN ... AND ... THEN 3 -- group3
                     ELSE NULL
                 END AS group_number
           FROM orders
       ) AS t
 WHERE group_number IS NOT NULL
 GROUP
    BY group_number
;

(the subquery isn't actually necessary, but I think it makes it clearer what's going on).

Upvotes: 1

Related Questions