SELECT DISTINCT TWO COLUMNS and SUM another COLUMN's VALUES

Question

Given two Tables that are Linked together

tbl_Gasoline 
--------------
ID | Type
-------------
1  | Diesel
2  | Kerosene

and

tbl_Expense
-----------------------------
ID | Price  | GasolineType (tbl_Gasoline foreign key)
-----------------------------
1  | 5000   | 1
2  | 4000   | 2
3  | 3000   | 1

I want to have an Output like this

tbl_GasolineExpense
----------------------------
ID |  Price  | Type
----------------------------
1  |  8000   | Diesel
2  |  4000   | Kerosene

I have tried to use a DISTINCT and SUM clauses but I can't seem to make a good query. It's been long since I have used SQL so some help would really be appreciated.

Clément Prévost · Accepted Answer · 2014-11-23 11:54:49Z

1

Try this one

SELECT e.id, SUM(price) AS 'price', g.NAME 
FROM tbl_expense e
INNER JOIN tbl_gasoline g ON e.GasolineType = g.id 
GROUP BY e.id, g.NAME

answered Nov 23, 2014 at 11:54

Clément Prévost

8,6702 gold badges39 silver badges55 bronze badges

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

Comments

S. M. Mohiuddin · Accepted Answer · 2014-11-23 12:36:35Z

0

Please Try this.

SELECT
e.id,
SUM(price) AS 'price',
g. NAME
FROM
    tbl_expense e
INNER JOIN tbl_gasoline g ON e.GasolineType = g.id
GROUP BY
    g.id,g. NAME

edited Nov 23, 2014 at 12:36

answered Nov 23, 2014 at 11:22

S. M. Mohiuddin

3883 silver badges10 bronze badges

1 Comment

response.write Over a year ago

Thanks @S. M Mohiuddin, but I have encountered some error when using the code saying that e.id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Luc M · Accepted Answer · 2014-11-23 12:56:38Z

0

SELECT tbl_Gasoline.ID,SUM(tbl_Expense.Price),tbl_Gasoline.Type 
from tbl_Expense inner join tbl_Gasoline on tbl_Expense.GasolineType = tbl_Gasoline.ID 
group by tbl_Gasoline.ID

edited Nov 23, 2014 at 12:56

Luc M

17.5k27 gold badges78 silver badges91 bronze badges

answered Nov 23, 2014 at 11:45

nickdnk

4,3494 gold badges27 silver badges45 bronze badges

Comments

Edgar Griñant · Accepted Answer · 2020-04-17 10:48:17Z

0

select 
  g.ID,
  E.SUM(PRICE) as price,
  e.GasolineType 
from 
  tbl_Gasoline  g,tbl_Expense e 
where 
  g.ID=e.GasolineType group by e.GasolineType

edited Apr 17, 2020 at 10:48

Edgar Griñant

6071 gold badge13 silver badges27 bronze badges

answered Nov 23, 2014 at 11:33

Uma Kalyani

1114 bronze badges

Collectives™ on Stack Overflow

SELECT DISTINCT TWO COLUMNS and SUM another COLUMN's VALUES

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related