Combining sum() of similar entry's, and including a WHERE clause SQL

Question

I am trying to add up the the numbers from amount, if the booking_id matches. I have been having trouble with summing these numbers as well as adding in a WHERE clause.

This is my code so far

At this time it is summing the numbers correctly, although if I add in the where it doesn't show any results

select *, Sum(amount) FROM payment group by booking_id where booking_id = 1

I have tried some methods like adding queries in queries but I have had no luck. The results I'm attempting to get are below.

booking_id   amount
----------------------
      1    |  5
      2    |  6
      1    |  6
      3    |  2
      3    |  3
      4    |  4

Output should be:

booking_id   amount
    ----------------------
          1    |  11
          2    |  6
          3    |  5
          4    |  4

I am attempting to group the results so that booking_id with a value of 1 will return a sum of 5+6.

My goal is to be able to add together the amounts that have the same booking id. As well as to be able to include a WHERE clause within this query

Skip the WHERE. Do GROUP BY to get all booking_id's as requested. — jarlh, Nov 13 '18 at 10:33
What @jarlh says is correct http://www.sqlfiddle.com/#!9/e59e0c/3 — Nick, Nov 13 '18 at 10:34

jarlh · Accepted Answer · 2018-11-13T10:38:20.287

1

To get all booking_id's as requested, simply skip the WHERE clause:

select booking_id, Sum(amount)
FROM payment
group by booking_id

To get all booking_id's for a specific ident (as later requested), add the WHERE clause:

select booking_id, Sum(amount)
FROM payment
where client_id = 123
group by booking_id

edited Nov 13 '18 at 10:38

answered Nov 13 '18 at 10:34

jarlh

42,561
8
45
63

That would be good, although I need to only get these results from a specific person. For example if client_id was in the database, and I want to only include that specific client. – Jackbeeby Nov 13 '18 at 10:36
I checked the link and it does seam to be working. Its just not wanting to work with what I have through. must be something I'm missing. Thanks for your help. – Jackbeeby Nov 13 '18 at 10:40
It really should be working, but for some reason their is something stoping it. Thanks for your help. – Jackbeeby Nov 13 '18 at 10:46

score 0 · Answer 2 · answered Nov 13 '18 at 10:31

0

Where should be before group by

select booking_id , Sum(amount) FROM payment 
where booking_id = 1
group by booking_id

answered Nov 13 '18 at 10:31

Fahmi

37,315
5
22
31

Zaynul Abadin Tuhin · Answer 3 · 2018-11-13T10:48:18.823

0

you did wrong only in order to write sql

   select booking_id, Sum(amount) FROM payment
   where client_id = 1 -- your condition 
   group by booking_id

Check this link for order

edited Nov 13 '18 at 10:48

answered Nov 13 '18 at 10:31

Zaynul Abadin Tuhin

31,407
5
33
63

Combining sum() of similar entry's, and including a WHERE clause SQL

3 Answers3