CODEWITHSUNDEEP
sqlite
BlackSwan
BlackSwan

Reputation: 25

where clause in nested select statement based on group by parameters

I would like to form an sql query something like below but can not figure out how to make it work:

SELECT col_1 AS a,
       col_2 AS b,
       (SELECT sum(col_3)
        FROM table2
        WHERE col_1 = a
          AND col_2 = b) AS c
FROM table1
GROUP BY col_1, col_2;

Upvotes: 1

Views: 44

Answers (1)

CL.
CL.

Reputation: 180121

Column aliases are not available in a subquery.

You have to refer to the columns directly:

SELECT col_1 AS a,
       col_2 AS b,
       (SELECT sum(col_3)
        FROM table2
        WHERE col_1 = table1.col_1
          AND col_2 = table1.col_2) AS c
FROM table1
GROUP BY col_1, col_2;

Upvotes: 1

Related Questions