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