代码之家 › 专栏 › 技术社区 › user8274065

如何在不重复的情况下求不同值的和

postgresql

user8274065 · 技术社区 · 8 年前

如何在不重复列上不同值的情况下求不同值但相同ID的和?

我在SQL命令中的输入。

    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;

我的结果。

 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

我的预期结果。

 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

我认为原因来自命令组,但如果我不按价格编写组,它将抛出一个错误。

2 回复 | 直到 8 年前

Geo 8 年前

也许可以使用SUM()函数。请看下面的链接,也许你也是这样:
how to group by and return sum row in Postgres

Kaushik Nayak 8 年前

你已经排除了 course_price 在当前结果和预期结果中列出。看来你把这个错误地包括在 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;