How to do sum of different values without duplicate

Question

How to do a sum of different values but same ID without duplicate different values on a column?

My Input in SQL Command.

    SELECT
      students.id        AS student_id,
      students.name,
      COUNT(*)           AS enrolled,
      c2.price           AS course_price,
      (COUNT(*) * price) AS paid
    FROM students
      LEFT JOIN enrolls e on students.id = e.student_id
      LEFT JOIN courses c2 on e.course_id = c2.id
    WHERE student_id NOTNULL
    GROUP BY students.id, students.name, c2.price
    ORDER BY student_id ASC;

My result.

 student_id |        name         | enrolled | paid 
------------+---------------------+----------+------
       1001 | Gulbadan Bálint     |        1 |   90
       1002 | Hanna Adair         |        5 |  450
       1003 | Taddeo Bhattacharya |        1 |   90
       1004 | Persis Havlíček     |        1 |   75
       1004 | Persis Havlíček     |        5 |  450
       1005 | Tory Bateson        |        1 |   90
       1007 | Dávid Fèvre         |        1 |   90
       1008 | Masuyo Stoddard     |        1 |   90
       1009 | Iiris Levitt        |        1 |   75
       1009 | Iiris Levitt        |        2 |  180
       1013 | Artair Kovač        |        1 |   30
       1013 | Artair Kovač        |        1 |   90
       1015 | Matilda Guinness    |        2 |  180
       1017 | Margarita Ek        |        1 |   90
       1018 | Misti Zima          |        3 |  270
       1019 | Conall Ventura      |        1 |   90
       1020 | Vivian Monday       |        2 |  180

My expected result.

 student_id |        name         | enrolled | paid 
------------+---------------------+----------+------
       1001 | Gulbadan Bálint     |        1 |   90
       1002 | Hanna Adair         |        5 |  450
       1003 | Taddeo Bhattacharya |        1 |   90
       1004 | Persis Havlíček     |        6 |  525
       1005 | Tory Bateson        |        1 |   90
       1007 | Dávid Fèvre         |        1 |   90
       1008 | Masuyo Stoddard     |        1 |   90
       1009 | Iiris Levitt        |        3 |  255
       1013 | Artair Kovač        |        2 |  120
       1015 | Matilda Guinness    |        2 |  180
       1017 | Margarita Ek        |        1 |   90
       1018 | Misti Zima          |        3 |  270
       1019 | Conall Ventura      |        1 |   90
       1020 | Vivian Monday       |        2 |  180

I think that the cause come from a GROUP BY command but it will throw an error if I do not write a GROUP BY price.

score 0 · Accepted Answer · answered Jun 04 '18 at 08:30

0

Perhaps you can use SUM() function. Please see link below, maybe it's same case with you:
how to group by and return sum row in Postgres

answered Jun 04 '18 at 08:30

Geo

16
4

score 0 · Answer 2 · answered Jun 04 '18 at 08:36

0

You have excluded course_price column both in your current and expected result. It seems you had wrongly included that in group by.

SELECT
  students.id        AS student_id,
  students.name,
  COUNT(*)           AS enrolled,
  --c2.price         AS course_price, --exclude this in o/p?
  (COUNT(*) * price) AS paid
FROM students
  LEFT JOIN enrolls e on students.id = e.student_id
  LEFT JOIN courses c2 on e.course_id = c2.id
WHERE student_id NOTNULL
GROUP BY students.id, students.name --,c2.price --and remove it from here 
ORDER BY student_id ASC;

answered Jun 04 '18 at 08:36

Kaushik Nayak

30,772
5
32
45

ERROR: column "c2.price" must appear in the GROUP BY clause or be used in an aggregate function. (COUNT(*) * price) AS paid – Jun 04 '18 at 11:02
@PrasitTongpradit : I think you didn't understand what I was saying. if you include `c2.price` in your select you should include it in `group by` and you can't include it in select (without aggregation)if you have not specified it in group by. If you want to understand how `group by` works, read a tutorial https://www.w3schools.com/sql/sql_groupby.asp – Kaushik Nayak Jun 04 '18 at 11:06
Oh, Thank you guy – Jun 04 '18 at 11:16

How to do sum of different values without duplicate

2 Answers2