CODEWITHSUNDEEP
javamysqlsqlswingjtable
JLink
JLink

Reputation: 307

SQL Select Query Error

Here I have have a table

expenses

with columns

In my jtable I want to show it like this enter image description here

I used this sql code the

SELECT vehicle_id,SUM(amount) FROM expenses GROUP BY type_id;

And I got output as

enter image description here

But I want it like previous image (the long one).. What can I do to this ?

Note

Here I used Sum for get multiple amount values.type_id used to denote various expenses type like diesel, oil, service..

Upvotes: 1

Views: 56

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can do it using conditional aggregation:

SELECT vehicle_id, 
       SUM(CASE WHEN type_id = 1 THEN amount ELSE 0 END) AS Diesel,
       SUM(CASE WHEN type_id = 2 THEN amount ELSE 0 END) AS Insurance,
       SUM(CASE WHEN type_id = 3 THEN amount ELSE 0 END) AS Tyre,
       SUM(CASE WHEN type_id = 4 THEN amount ELSE 0 END) AS Battery,
       SUM(CASE WHEN type_id = 5 THEN amount ELSE 0 END) AS Oil,
       SUM(CASE WHEN type_id = 6 THEN amount ELSE 0 END) AS Garage
FROM expenses 
GROUP BY vehicle_id;

You need to GROUP BY only vehicle_id. Then perform conditional SUM operations depending on type_id. This way you can get partial sums for type_id per vehicle_id.

Demo here

Upvotes: 1

Related Questions