CODEWITHSUNDEEP
sqlsum
jadi_78
jadi_78

Reputation: 23

Using the function SUM?

I have this query:

Select t1.col5,
       t1.col6,
       SUM(CASE WHEN t2.col1 = 'A' THEN t2.col2 ELSE 0
                WHEN t2.col1 = 'B' THEN t2.col2 * -1 ELSE 0
       END) AS price,
       SUM(CASE WHEN t2.col1 = 'C' THEN t2.col2 + 100  ELSE 0
       END) AS bonus
       FROM t1, t2
       WHERE t1.col1 = t2.col4
       AND (price + bonus ) <> 0
      GROUP BY  t1.col5,
                t1.col6

But I can't do (price + bonus ) <> 0? I have this message: price invalid identifier! How can I do to use the names of my SUM in a WHERE?

Upvotes: 0

Views: 49

Answers (3)

David Faber
David Faber

Reputation: 12486

You can't use column aliases (or, for that matter, aggregates) in the WHERE clause. So what you want to do is use a subquery OR put the aggregates in a HAVING clause. I'll show the subquery below, which I think is easier since you don't have to reproduce all the keystrokes (you need to use the aggregates themselves, and not their aliases, in a HAVING clause):

SELECT * FROM (
    Select t1.col5,
           t1.col6,
           SUM(CASE WHEN t2.col1 = 'A' THEN t2.col2 ELSE 0
               WHEN t2.col1 = 'B' THEN t2.col2 * -1 ELSE 0
           END) AS price,
           SUM(CASE WHEN t2.col1 = 'C' THEN t2.col2 + 100  ELSE 0
           END) AS bonus
       FROM t1, t2
      WHERE t1.col1 = t2.col4
      GROUP BY  t1.col5,
            t1.col6
) WHERE (price + bonus ) <> 0

Upvotes: 0

Andrew
Andrew

Reputation: 8693

Constraining on aggregate functions needs to be done in a HAVING clause:

Select t1.col5,
       t1.col6,
       SUM(CASE WHEN t2.col1 = 'A' THEN t2.col2 ELSE 0
                WHEN t2.col1 = 'B' THEN t2.col2 * -1 ELSE 0
       END) AS price,
       SUM(CASE WHEN t2.col1 = 'C' THEN t2.col2 + 100  ELSE 0
       END) AS bonus
       FROM t1, t2
       WHERE t1.col1 = t2.col4

      GROUP BY  t1.col5,
                t1.col6
    having SUM(CASE WHEN t2.col1 = 'A' THEN t2.col2 ELSE 0
                WHEN t2.col1 = 'B' THEN t2.col2 * -1 ELSE 0
       END) + SUM(CASE WHEN t2.col1 = 'C' THEN t2.col2 + 100  ELSE 0
       END) <> 0

Upvotes: 1

macoms01
macoms01

Reputation: 1110

You can't use that alias - check out the order of operations in a sql query: http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

You need to try something like this:

Select t1.col5,
       t1.col6,
       SUM(CASE WHEN t2.col1 = 'A' THEN t2.col2 ELSE 0
                WHEN t2.col1 = 'B' THEN t2.col2 * -1 ELSE 0
       END) AS price,
       SUM(CASE WHEN t2.col1 = 'C' THEN t2.col2 + 100  ELSE 0
       END) AS bonus
       FROM t1, t2
       WHERE t1.col1 = t2.col4
       AND (SUM(CASE WHEN t2.col1 = 'A' THEN t2.col2 ELSE 0 WHEN t2.col1 = 'B' THEN t2.col2 * -1 ELSE 0 END)
            +
           SUM(CASE WHEN t2.col1 = 'C' THEN t2.col2 + 100  ELSE 0 END)) <> 0
          GROUP BY  t1.col5,
                    t1.col6

Upvotes: 0

Related Questions