Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-23-2008
04:45 PM

12-23-2008
04:45 PM

Here's another question which I'm sure is an easy one...

Let's say I have a table with the following data...

A 2

A 3

A 1

A 6

B 3

B -3

If I wanted to sum up and group them by the first column, I'd get...

A 12

B 0

Well, what if I didn't want to have the B shown if the SUM was 0. The error I'm getting now is "Improper use of an aggregate function in a WHERE clause", but I'm unsure how to get it in there.

I have this under my SELECT statement: SUM(Chrge_Amt) AS Total

And I've tried both of the following under my WHERE statement:

SUM(Chrge_Amt) <> 0

Total <> 0

How can I accomplish this?

Let's say I have a table with the following data...

A 2

A 3

A 1

A 6

B 3

B -3

If I wanted to sum up and group them by the first column, I'd get...

A 12

B 0

Well, what if I didn't want to have the B shown if the SUM was 0. The error I'm getting now is "Improper use of an aggregate function in a WHERE clause", but I'm unsure how to get it in there.

I have this under my SELECT statement: SUM(Chrge_Amt) AS Total

And I've tried both of the following under my WHERE statement:

SUM(Chrge_Amt) <> 0

Total <> 0

How can I accomplish this?

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-23-2008
05:10 PM

12-23-2008
05:10 PM

Try a the HAVING clause

SELECT gr, SUM(chrg_amt)

GROUP BY gr

HAVING SUM(chrg_amt) <> 0

ORDER BY gr;

SELECT gr, SUM(chrg_amt)

GROUP BY gr

HAVING SUM(chrg_amt) <> 0

ORDER BY gr;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-02-2009
10:16 AM

01-02-2009
10:16 AM

For aggregate functions we have to use having for any relational operation.

try with having inspite of where

try with having inspite of where

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.