0

Am trying to write a query to display all the vehicles fuel consumption per month from January to December depending with the year picked by a user. the objective is to get something like this.

+-------------+---------------------------------------------------------+
| Vehicle | Jan | Feb | Mar | Arp | May | Jun | Jul | Aug | Sep |...|
+-------------+---------------------------------------------------------+
| NY001   | 30  | 44  | 54  | 98  | 12  | 37  | 76  |     | 32  |...|
| NY002   | 30  | 65  | 99  |     | 12  | 85  |     | 13  | 52  |...|
| NY003   | 30  | 95  | 24  | 98  |     | 55  | 76  | 43  |     |...|
| NY004   |     | 22  | 97  |     | 12  | 28  |     |     | 12  |...|
| NY005   | 30  | 36  | 15  | 98  | 12  | 65  | 36  | 23  | 66  |...|
| NY006   |     | 76  | 34  | 98  | 12  | 75  | 16  | 93  | 97  |...|
+-------------+---------------------------------------------------------+

My table syntax is like below

CREATE TABLE `COSTING` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Vehicle` varchar(100) NOT NULL DEFAULT '',
  `Fueldate` datetime NOT NULL,
  `Costcenter` varchar(200) NOT NULL DEFAULT '',
  `Code` varchar(100) NOT NULL DEFAULT '',
  `Cost` int(100) NOT NULL DEFAULT '0',
  `FuelLitters` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;

How do i do this using mysql query. Please help me. am new in this.

Sample data:

Vehicle | Fueldate | Cost | FuelLitters
----------------------------------------
s213    2016-11-10  17536   96.87   
S055    2016-11-23  350     12.66   
s213    2016-11-15  7204    33.87       
s213    2016-11-24  10827   34.66       
S530    2016-11-29  4063    42.81       
s515    2016-11-26  20599   250     
S513    2016-11-26  9108    110.94      
S213    2016-12-29  4400    46.36       
S212    2016-11-29  6708    70.68       
S211    2016-12-29  3059    32.23       
S210    2016-11-28  3950    41.62       
S513    2016-12-29  2428    25.58       
s160    2016-11-25  4002    42.17       
MGT02T  2016-11-26  20804   219.21
Rhynix
  • 73
  • 6

1 Answers1

-1

Edit

After shadow's comment here another solution without subquery :

select c.Vehicle, 
sum(case when DATE_FORMAT(Fueldate, "%c") = 1 then FuelLitters end) as JAN,
sum(case when DATE_FORMAT(Fueldate, "%c") = 2 then FuelLitters end) as FEB,
sum(case when DATE_FORMAT(Fueldate, "%c") = 3 then FuelLitters end) as MAR,
sum(case when DATE_FORMAT(Fueldate, "%c") = 4 then FuelLitters end) as APR,
sum(case when DATE_FORMAT(Fueldate, "%c") = 5 then FuelLitters end) as MAY,
sum(case when DATE_FORMAT(Fueldate, "%c") = 6 then FuelLitters end) as JUN,
sum(case when DATE_FORMAT(Fueldate, "%c") = 7 then FuelLitters end) as JUL,
sum(case when DATE_FORMAT(Fueldate, "%c") = 8 then FuelLitters end) as AUG,
sum(case when DATE_FORMAT(Fueldate, "%c") = 9 then FuelLitters end) as SEP,
sum(case when DATE_FORMAT(Fueldate, "%c") = 10 then FuelLitters end) as OCT,
sum(case when DATE_FORMAT(Fueldate, "%c") = 11 then FuelLitters end) as NOV,
sum(case when DATE_FORMAT(Fueldate, "%c") = 12 then FuelLitters end) as `DEC`
from COSTING c
group by Vehicle

For explanations : you have not the columns in your table, so you have to calculate it on the fly to get result. For each month we create a case when selection to get only the data from the month. Grouping it by vehicle at the end allow us to make the sum by vehicle.

Yoleth
  • 1,269
  • 7
  • 15
  • @Yoleth, You are the best!!!! you nailed it, i think ill put this query as a wall paper in my computer too. Thanks so so so so much !!! you are the best!!! – Rhynix Dec 15 '16 at 11:08
  • And this is exactly the same solution as described in the duplicate topic link. This is why this one should be closed as duplicate. – Shadow Dec 15 '16 at 11:16
  • @Yoleth If i introduced a fueling station in the table and wanted to filter by it, is there a way of doing that so that i can see the same analysis but only for a specific fuel station?, thanks you – Rhynix Dec 15 '16 at 12:05
  • Oh, found a solution mate, thanks – Rhynix Dec 15 '16 at 12:11
  • `MONTH()` is shorter than `DATE_FORMAT()`. – Rick James Dec 16 '16 at 00:38