CODEWITHSUNDEEP
mysql
Bhavesh Chauhan
Bhavesh Chauhan

Reputation: 1053

How to Group by boolen value in mysql

I have observation table Like

 id     ignition   mileage`**

   1    true          10
   2    true          30
   3    true          10
   4    false         05
   5    false         05
   6    true          10
   7    true          10
   8    false         20
   9    false         20
   10   false         20

I would like to calculate sum of mileage by grouping ignition true and false value.something like

ignition   total_mileage 
true       50
false      10
true       20
false      60

How can i get output using grouping

Upvotes: 0

Views: 30

Answers (1)

Strawberry
Strawberry

Reputation: 33945

A purist might point out a couple of flaws in this approach, but I'm trying to keeping it concise...

E.g.:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,ignition TINYINT NOT NULL
,mileage INT NOT NULL
);

INSERT INTO my_table VALUES
( 1,true,10),
( 2,true ,30),
( 3,true ,10),
( 4,false,05),
( 5,false,05),
( 6,true ,10),
( 7,true ,10),
( 8,false,20),
( 9,false,20),
(10,false,20);

SELECT ignition
     , SUM(mileage) total 
  FROM 
     ( SELECT x.*
            , CASE WHEN ignition = @prev THEN @i:=@i ELSE @i:=@i+1 END i
            , @prev:=ignition FROM my_table x
            ,(SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY id
     ) n 
 GROUP 
    BY i;
+----------+-------+
| ignition | total |
+----------+-------+
|        1 |    50 |
|        0 |    10 |
|        1 |    20 |
|        0 |    60 |
+----------+-------+

Upvotes: 1

Related Questions