SQL Select Query Error

Question

Here I have have a table

expenses

with columns

id (PK)
type_id (FK)
amount
date
vehicle_id (FK)

In my jtable I want to show it like this

I used this sql code the

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

And I got output as

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..

how did your second picture become the output of that select statement which only returns 2 columns ? — Drew
– Drew, Commented Oct 15, 2015 at 16:46
I don't know. When I do it Navicat, it also shows the same output. Any thing wrong ? — JLink
– JLink, Commented Oct 15, 2015 at 16:49
Since it's more SQL question than Java or UI I would advise to add sql tag to list of tags. About question: did you consider using left joins in your SQL query? — rsutormin
– rsutormin, Commented Oct 15, 2015 at 16:50
No. I have no idea about it..Can't we get it from a SELECT query.. — JLink
– JLink, Commented Oct 15, 2015 at 16:53

Giorgos Betsos · Accepted Answer · 2015-10-15 17:03:14Z

1

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

edited Oct 15, 2015 at 17:03

answered Oct 15, 2015 at 16:56

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JLink Over a year ago

thank you very much..It works nicely..I fed up with this since 5 days..thank you very much again..

Collectives™ on Stack Overflow

SQL Select Query Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related