All Forums Database
txwylde 5 posts Joined 06/10
09 Jul 2014
Creating a Total

I have a column that counts the amounts of serial numbers in a column. This column can have multiple serials separated by a comma.
Here is the SQL to create this column:
(char_length(serial_number) - char_length(apputil_user.oreplace(serial_number, ','))) + 1 as num_serials
 
Is there a way to SUM this field? I am looking to create a chart that has the Date, who requested it and the sum of all the serials. Is there a way to easily do this? I have tried to create a Case Statement and encasing it with a SUM but I get an error about aggregates.

Thanks!
Bill
 

dnoeth 4628 posts Joined 11/04
10 Jul 2014

Hi Bill,
did you try

SUM((char_length(serial_number) - char_length(apputil_user.oreplace(serial_number, ','))) + 1)

If this is not what you want you need to add more details.

Dieter

txwylde 5 posts Joined 06/10
11 Jul 2014

I tried that. I get the error: 

 Selected non-aggregate values must be part of the associated group.

 

If you try to group by a Date or the a person id, you get the same error.

 

Here is my query:

select 
cast(date_processed as date),
initiator,
SUM((char_length(serial_number) - char_length(apputil_user.oreplace(serial_number, ','))) + 1) as num_serials
from datamining_biz_app4.legal_data_audit
where cast(date_processed as date) = current_date - 7
and serial_number is not null
and serial_number <> ' '
order by 1;

 

 

Thanks!

 

dnoeth 4628 posts Joined 11/04
11 Jul 2014

Hi Bill,
of course you need to add a GROUP BY 1,2

Dieter

txwylde 5 posts Joined 06/10
11 Jul 2014

Thanks Dieter. Totally forget about Grouping by the initiator. :)

You must sign in to leave a comment.