Grouping by two values

Question

Hello I have this table: (id, carId, gasStationId, liters, totalPrice). I would like to create query for sum total costs in each gas stations by car. I know how to sum total costs by gas station, but I don't know how to group by car. Here is my query:

select sum(totalPrice) as totalCosts
     , count(*) as countPurchases
     , g.name
     , p.carId 
  from purchase as p
  join gas_station as g  
    on g.id = p.id
 group 
    by gasStationId

I would like get this result:

┌─────────────┬──────┬──────┐
│ GasStation1 │ Car1 │ 1000 │
├─────────────┼──────┼──────┤
│ GasStation1 │ Car2 │ 1500 │
│ GasStation2 │ Car2 │  500 │
│ GasStation2 │ Car1 │  700 │
└─────────────┴──────┴──────┘

Do you mean something like group by gasStationId, carId or are you wanting to join another table you're not showing? — Joachim Isaksson
– Joachim Isaksson, Commented Dec 10, 2018 at 17:48
Side note: Why do you have gasStationId in your grouping? It looks like it should've been g.id. But I following your query in my answer. — Racil Hilan
– Racil Hilan, Commented Dec 10, 2018 at 17:57

Racil Hilan · Accepted Answer · 2018-12-10 17:48:56Z

1

It's the same thing, just add p.carId to the grouping separated by a comma:

GROUP BY gasStationId, p.carId

So for the results in your question, you can do:

SELECT g.name, p.carId, SUM(totalPrice) AS totalCosts
FROM purchase AS p
JOIN gas_station AS g ON g.id = p.id
GROUP BY gasStationId, p.carId

answered Dec 10, 2018 at 17:48

Racil Hilan

25.4k13 gold badges56 silver badges61 bronze badges

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

Comments

Strawberry · Accepted Answer · 2018-12-10 18:41:52Z

0

A good idea is to use window functions: https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

something like:

SELECT
     gasStationId,
     SUM(totalPrice) OVER(PARTITION BY carId) AS price_by_car
   FROM purchase p
   JOIN gasStations g
   ON g.id=p.gassStationId;

edited Dec 10, 2018 at 18:41

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Dec 10, 2018 at 17:49

Frank Conry

2,7483 gold badges32 silver badges41 bronze badges

2 Comments

Racil Hilan Over a year ago

Where did you get profit from? And in the output example, the OP has one total, not two.

Frank Conry Over a year ago

I'll fix it. Just added the total per station for completeness.

Collectives™ on Stack Overflow

Grouping by two values

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related