CODEWITHSUNDEEP
sqlamazon-redshift
FloatingRock
FloatingRock

Reputation: 7065

SQL: Subquery filter by parent field

I have the following SQL query, that I can't get to work.

Data Schema

| Timestamp                | Status   | Amount |
|--------------------------|----------|--------|
| 2016-03-23T08:03:34.204Z | Approved |   100  |
| 2016-03-23T16:00:06.755Z | Declined |   450  |
| 2016-03-30T10:18:43.846Z | Approved | 50010  |

Desired Output

| Date       | Approved Count | Declined Count |
|------------|----------------|----------------|
| 2016-03-23 | 1              | 1              |
| 2016-03-30 | 1              | 0              |

What I've tried

SELECT DATE(timestamp) AS Date,

  (SELECT COUNT(*)
   FROM db.transactions
   WHERE status='Approved'
     AND DATE(timestamp)=Date) AS Approved,

  (SELECT COUNT(*)
   FROM db.transactions
   WHERE status='Declined') AS Declined
FROM db.transactions a
GROUP BY Date;

.. which fails with the error message:

An error occurred with Amazon Redshift. column "date" does not exist

Upvotes: 0

Views: 236

Answers (2)

Hart CO
Hart CO

Reputation: 34774

The error you're getting is from trying to use a column alias defined in your SELECT clause in your GROUP BY clause, the alias is not available because GROUP BY is actually processed before SELECT. You could get rid of the error by using GROUP BY DATE(timestamp), but this can be done much more efficiently with conditional aggregation:

SELECT DATE(timestamp) AS Date
      ,SUM(CASE WHEN status='Approved' THEN 1 ELSE 0 END) AS Approved
      ,SUM(CASE WHEN status='Declined' THEN 1 ELSE 0 END) AS Declined
FROM db.transactions
GROUP BY  DATE(timestamp)

Upvotes: 1

Gayasuddin Usmani
Gayasuddin Usmani

Reputation: 47

    SELECT DATE(timestamp),

  (SELECT COUNT(*)
   FROM db.transactions
   WHERE status='Approved'
     AND DATE(timestamp) AS Approved,

  (SELECT COUNT(*)
   FROM db.transactions
   WHERE status='Declined') AS Declined
FROM db.transactions GROUP BY Date;

Upvotes: 0

Related Questions