CTE query fails with error about not known column name

2573 views sql
1

I'm learning CTE and I got caught up in doing one exercise. Please have a look at the following tables:

donation

    +----+------------+--------------+---------+------------+------------+
    | id | project_id | supporter_id | amount  | amount_eur | donated    |
    +----+------------+--------------+---------+------------+------------+
    | 1  | 4          | 4            | 928.40  | 807.70     | 2016-09-07 |
    +----+------------+--------------+---------+------------+------------+
    | 2  | 8          | 18           | 384.38  | 334.41     | 2016-12-16 |
    +----+------------+--------------+---------+------------+------------+
    | 3  | 6          | 12           | 367.21  | 319.47     | 2016-01-21 |
    +----+------------+--------------+---------+------------+------------+
    | 4  | 2          | 19           | 108.62  | 94.50      | 2016-12-29 |
    +----+------------+--------------+---------+------------+------------+
    | 5  | 10         | 20           | 842.58  | 733.05     | 2016-11-30 |
    +----+------------+--------------+---------+------------+------------+
    | 6  | 4          | 15           | 653.76  | 568.77     | 2016-08-05 |
    +----+------------+--------------+---------+------------+------------+

project

+----+------------+-----------+----------------+
| id | category   | author_id | minimal_amount |
+----+------------+-----------+----------------+
| 1  | music      | 1         | 1677           |
+----+------------+-----------+----------------+
| 2  | music      | 5         | 21573          |
+----+------------+-----------+----------------+
| 3  | travelling | 2         | 4952           |
+----+------------+-----------+----------------+
| 4  | travelling | 5         | 3135           |
+----+------------+-----------+----------------+
| 5  | travelling | 2         | 8555           |
+----+------------+-----------+----------------+

supporter

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1  | Marlene    | Wagner    |
+----+------------+-----------+
| 2  | Lonnie     | Goodwin   |
+----+------------+-----------+
| 3  | Sophie     | Peters    |
+----+------------+-----------+
| 4  | Edwin      | Paul      |
+----+------------+-----------+
| 5  | Hugh       | Thornton  |
+----+------------+-----------+
| 6  | Dianne     | Harris    |
+----+------------+-----------+

The aim is to show the first and last name of supporters with the number of not yet founded projects (sum of amount < minimal_amount) they created.

My SQL:

WITH projects AS
  ( SELECT first_name AS firstName,
           last_name AS lastName,
           project_id,
           sum(amount) AS amountSum,
           minimal_amount
   FROM donation d
   INNER JOIN project p ON p.id = d.project_id
   INNER JOIN supporter s ON s.id = p.author_id
   GROUP BY FIRST,
            LAST,
            project_id)
SELECT firstName,
       lastName,
       count(project_id)
FROM projects
WHERE amountSum < minimal_amount;

I gotERROR: column "first" does not exist error.

answered question

What's your dbms?

should your groupby be first_name and last_name?

It should be generic but I work with Postres. @garfbradaz it was my idea. There is no such requirement in the description of the exercise.

Why my question was downvoted?

1 Answer

13

you did wrong to mention column name in GROUP BY FIRST, LAST which is not exist in your table and minimal_amount also need to include in group by clause as you did not use any aggregation for that column

    WITH projects AS
      ( 
       SELECT first_name AS firstName,
               last_name AS lastName,
               project_id,
               sum(amount) AS amountSum,
               minimal_amount
       FROM donation d
       INNER JOIN project p ON p.id = d.project_id
       INNER JOIN supporter s ON s.id = p.author_id
       GROUP BY first_name ,
                last_name ,
                project_id,minimal_amount
)
    SELECT firstName,
           lastName,
           count(project_id)
    FROM projects
    WHERE amountSum < minimal_amount
   group by firstName,
           lastName

posted this

Have an answer?

JD

Please login first before posting an answer.