Skip to content
Advertisement

I have been looking for solution to get sum of multiple columns(for a single row) in Mysql to get total of every hour for electricity invoice?

I wrote below query to get sum but it is showing(something progressing) total as null for couple of records. It is showing 1064 error at line 4 when I include all records like below.

select 
Metering0,Metering1,Metering2,Metering3,Metering4,Metering5,Metering6,Metering7,Metering8,Metering9,Metering10,Metering11,Metering12,
Metering13,Metering14,Metering15,Metering16,Metering17,Metering18,Metering19,Metering20,Metering21,Metering22
total = (Metering0+Metering1+Metering2+Metering3+Metering4+Metering5+Metering6+Metering7+Metering8+Metering9+Metering10+Metering11+Metering12
+Metering13+Metering14+Metering15+Metering16+Metering17+Metering18+Metering19+Metering20+Metering21+Metering22)
from ireneene_calculationdb.invoice_template_year

Advertisement

Answer

it is showing total as null for couple of records

Probably, at least one of the columns has a null value for those records. In MySQL (and most other RDBMS), 1 + null yields null. You can work around this with coalesce():

coalesce(Metering0, 0) + coalesce(Metering1, 0) + coalesce(Metering2, 0) + ...

It is showing 1064 error at line 4

column_alias = <expression> is not valid MySQL syntax (this is a SQL Server syntax). You should put the column alias after the expression, optionaly preceeded by keyworkd as, like:

coalesce(Metering0, 0) + coalesce(Metering1, 0) + coalesce(Metering2, 0) + ... as total 
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement