Summing with blank values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Summing with blank values

Author

Topic

bd528
Starting Member

23 Posts

Posted - 2011-07-14 : 06:22:23

Hi,

I'm trying to sum some values from a table using the following :-

sum(invoice.costband1 + invoice.costband2)

This seems to work where adjecent "costbands" contain zero values within the table. But, if there are blank entries in the table, the sum does not total correctly.

However, if I run :-

sum(invoice.costband1)
sum(invoice.costband2)

...seperately, I get the expected totals.

Any ideas why this might be?

Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 06:56:46

When you add null to a non-null value you get a null. To get around that, you could use the coalesce or isnull function as:


sum(coalesce(invoice.costband1,0) + coalesce(invoice.costband2,0))

bd528
Starting Member

23 Posts

Posted - 2011-07-14 : 07:07:09

sunitabeck, thanks for your reply.

Unfortuantely, I only have limitied access to the tables I'm querying. When I try and use coalesce, I get a message basically saying I can't make changes to the database.

Are there any other possible solutions?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 07:12:34

If you have the required permissions to run the query without the coalesce, then you will have the permission to run it with the coalesce as well. The part of the query that you posted is not changing anything in the database, so the error message does not/should not be related to the change.

Can you post the exact error message and the full query?

bd528
Starting Member

23 Posts

Posted - 2011-07-14 : 07:20:46

The message I'm getting is :-

"Only System Administrators are allowed to run scripts that modify the database"

My query is below. Pleas excuse the poor formatting/coding - I'm new to SQL! :)

select 
site.Site_Name [Site Name], 
account.mpan [MPAN Core], 
account.Acctno [Account Number], 
sum(invoice.consband3) [ALL NON BUSINESS DAYS 0700-2400], 
sum(invoice.consband12) [ALL NIGHTS 0000-0700], 
sum(invoice.consband1) [APL-OCT BUSINESS DAYS 0700-2400], 
sum(invoice.consband2) [NOV-MAR BUSINESS DAYS 0700-2400], 
sum(invoice.consumption) [Consumption kWh], 
sum(invoice.costband1) [Cost Band 1], 
sum(invoice.costband2) [Cost Band 2], 
sum(invoice.costband3) [Cost Band 3], 
sum(invoice.costband4) [Cost Band 4], 
sum(invoice.costband5) [Cost Band 5], 
sum(invoice.costband6) [Cost Band 6], 
sum(invoice.costband7) [Cost Band 7], 
sum(invoice.costband8) [Cost Band 8],
sum(invoice.costband9) [Cost Band 9], 
sum(invoice.costband10) [Cost Band 10], 
sum(invoice.costband11) [Cost Band 11], 
sum(invoice.costband12) [Cost Band 12], 
sum(coalesce(invoice.costband1,0) + coalesce(invoice.costband2,0)),
sum(invoice.triadmdchg) [TUOS], 
sum(invoice.misccost1) [Settl.& Agent]
from invoice, account, site, invuos
where
account.acct_link = invoice.acct_link
and
account.customer_link = 1037    
and
site.site_link = account.site_link
and
invoice.utilno = 1
and 
invoice.invoicedatefrom between '2010-04-01 00:00:00.000' and '2011-03-01 00:00:00.000'
and
account.acct_link = INVUOS.acct_link
and 
invoice.invdate = INVUOS.invdate
Group by site.Site_Name, account.mpan, account.Acctno
Order by account.Acctno

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 07:51:16

I don't see anything that would cause this message to appear when you use the coalesce function, but would not if you didn't. Regardless, change your query to this to get around it:

sum(invoice.costband1) + sum(invoice.costband2)

This would still not work correctly if one of the sums happened to be null. Also, other possibilities are:


SUM( isnull(invoice.costband1, 0) + isnull(invoice.costband2, 0) )

isnull(sum(invoice.costband1),0) + isnull(sum(invoice.costband2),0)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 07:56:29

I still think that the error message you are getting has nothing to do with this query - that the problem is somewhere else. I have two queries below. Can you copy and run these and confirm that the first one generates the error message and the second one does not?

Query 1


SELECT
	SITE.Site_Name [Site Name],
	account.mpan [MPAN Core],
	account.Acctno [Account Number],
	SUM(invoice.consband3) [ALL NON BUSINESS DAYS 0700-2400],
	SUM(invoice.consband12) [ALL NIGHTS 0000-0700],
	SUM(invoice.consband1) [APL-OCT BUSINESS DAYS 0700-2400],
	SUM(invoice.consband2) [NOV-MAR BUSINESS DAYS 0700-2400],
	SUM(invoice.consumption) [Consumption kWh],
	SUM(invoice.costband1) [Cost Band 1],
	SUM(invoice.costband2) [Cost Band 2],
	SUM(invoice.costband3) [Cost Band 3],
	SUM(invoice.costband4) [Cost Band 4],
	SUM(invoice.costband5) [Cost Band 5],
	SUM(invoice.costband6) [Cost Band 6],
	SUM(invoice.costband7) [Cost Band 7],
	SUM(invoice.costband8) [Cost Band 8],
	SUM(invoice.costband9) [Cost Band 9],
	SUM(invoice.costband10) [Cost Band 10],
	SUM(invoice.costband11) [Cost Band 11],
	SUM(invoice.costband12) [Cost Band 12],
	SUM( COALESCE(invoice.costband1, 0) + COALESCE(invoice.costband2, 0) ),
	SUM(invoice.triadmdchg) [TUOS],
	SUM(invoice.misccost1) [Settl.& Agent]
FROM
	invoice,
	account,
	SITE,
	invuos
WHERE
	account.acct_link = invoice.acct_link
	AND account.customer_link = 1037
	AND SITE.site_link = account.site_link
	AND invoice.utilno = 1
	AND invoice.invoicedatefrom BETWEEN '2010-04-01 00:00:00.000' AND 
	    '2011-03-01 00:00:00.000'
	AND account.acct_link = INVUOS.acct_link
	AND invoice.invdate = INVUOS.invdate
GROUP BY
	SITE.Site_Name,
	account.mpan,
	account.Acctno
ORDER BY
	account.Acctno

Query 2

SELECT
	SITE.Site_Name [Site Name],
	account.mpan [MPAN Core],
	account.Acctno [Account Number],
	SUM(invoice.consband3) [ALL NON BUSINESS DAYS 0700-2400],
	SUM(invoice.consband12) [ALL NIGHTS 0000-0700],
	SUM(invoice.consband1) [APL-OCT BUSINESS DAYS 0700-2400],
	SUM(invoice.consband2) [NOV-MAR BUSINESS DAYS 0700-2400],
	SUM(invoice.consumption) [Consumption kWh],
	SUM(invoice.costband1) [Cost Band 1],
	SUM(invoice.costband2) [Cost Band 2],
	SUM(invoice.costband3) [Cost Band 3],
	SUM(invoice.costband4) [Cost Band 4],
	SUM(invoice.costband5) [Cost Band 5],
	SUM(invoice.costband6) [Cost Band 6],
	SUM(invoice.costband7) [Cost Band 7],
	SUM(invoice.costband8) [Cost Band 8],
	SUM(invoice.costband9) [Cost Band 9],
	SUM(invoice.costband10) [Cost Band 10],
	SUM(invoice.costband11) [Cost Band 11],
	SUM(invoice.costband12) [Cost Band 12],
	SUM( invoice.costband1 + invoice.costband2 ),
	SUM(invoice.triadmdchg) [TUOS],
	SUM(invoice.misccost1) [Settl.& Agent]
FROM
	invoice,
	account,
	SITE,
	invuos
WHERE
	account.acct_link = invoice.acct_link
	AND account.customer_link = 1037
	AND SITE.site_link = account.site_link
	AND invoice.utilno = 1
	AND invoice.invoicedatefrom BETWEEN '2010-04-01 00:00:00.000' AND 
	    '2011-03-01 00:00:00.000'
	AND account.acct_link = INVUOS.acct_link
	AND invoice.invdate = INVUOS.invdate
GROUP BY
	SITE.Site_Name,
	account.mpan,
	account.Acctno
ORDER BY
	account.Acctno

bd528
Starting Member

23 Posts

Posted - 2011-07-14 : 08:02:23

Thank you for your efforts!

I ran both queries, and both gave the error message.

I tried the isnull option, which almost worked, but I was forced to group by invoice.costband2, when my results need to be grouped by account.Acctno.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 08:13:40

Two things:

1. The permission issue is completely unrelated to the coalesce. How are you running the query? From an SSMS window? Or are you using some kind of interface to send the query to SQL Server. That error message does not look familiar - so it may not even be coming from SQL server.

2. Neither of the code fragments that I posted which use ISNULL function should force you to group by costband2. If SQL server is complaining about that, you have a reference to costband2 somewhere else in the query, outside of an aggregate function.

bd528
Starting Member

23 Posts

Posted - 2011-07-14 : 08:27:30

1. Yes, I use an interface to run the query. Perhaps coalesce is a "banned" function for me?

2. Below is the code is used that included isnull :-

select 
site.Site_Name [Site Name], 
account.mpan [MPAN Core], 
account.Acctno [Account Number], 
sum(invoice.consband3) [ALL NON BUSINESS DAYS 0700-2400], 
sum(invoice.consband12) [ALL NIGHTS 0000-0700], 
sum(invoice.consband1) [APL-OCT BUSINESS DAYS 0700-2400], 
sum(invoice.consband2) [NOV-MAR BUSINESS DAYS 0700-2400], 
sum(invoice.consumption) [Consumption kWh], 
sum(invoice.costband1) [Cost Band 1], 
sum(invoice.costband2) [Cost Band 2], 
sum(invoice.costband3) [Cost Band 3], 
sum(invoice.costband4) [Cost Band 4], 
sum(invoice.costband5) [Cost Band 5], 
sum(invoice.costband6) [Cost Band 6], 
sum(invoice.costband7) [Cost Band 7], 
sum(invoice.costband8) [Cost Band 8],
sum(invoice.costband9) [Cost Band 9], 
sum(invoice.costband10) [Cost Band 10], 
sum(invoice.costband11) [Cost Band 11], 
sum(invoice.costband12) [Cost Band 12], 
sum(isnull(invoice.costband1,0)) + isnull(invoice.costband2,0),
sum(invoice.triadmdchg) [TUOS], 
sum(invoice.misccost1) [Settl.& Agent],
invuos.costband1
from invoice, account, site, invuos
where
account.acct_link = invoice.acct_link
and
account.customer_link = 1037    
and
site.site_link = account.site_link
and
invoice.utilno = 1
and 
invoice.invoicedatefrom between '2010-04-01 00:00:00.000' and '2011-03-01 00:00:00.000'
and
account.acct_link = INVUOS.acct_link
and 
invoice.invdate = INVUOS.invdate
Group by site.Site_Name, account.mpan, account.Acctno
Order by account.Acctno

The above fails because I need to group by costband2. I do have another reference to costband2. Is only one allowed?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 08:33:05

The general rule is that any column that is outside of an aggregate function must be also in the group by list. In the code you have the second term outside the SUM function - see the red comment below.

select 
site.Site_Name [Site Name], 
account.mpan [MPAN Core], 
account.Acctno [Account Number], 
sum(invoice.consband3) [ALL NON BUSINESS DAYS 0700-2400], 
sum(invoice.consband12) [ALL NIGHTS 0000-0700], 
sum(invoice.consband1) [APL-OCT BUSINESS DAYS 0700-2400], 
sum(invoice.consband2) [NOV-MAR BUSINESS DAYS 0700-2400], 
sum(invoice.consumption) [Consumption kWh], 
sum(invoice.costband1) [Cost Band 1], 
sum(invoice.costband2) [Cost Band 2], 
sum(invoice.costband3) [Cost Band 3], 
sum(invoice.costband4) [Cost Band 4], 
sum(invoice.costband5) [Cost Band 5], 
sum(invoice.costband6) [Cost Band 6], 
sum(invoice.costband7) [Cost Band 7], 
sum(invoice.costband8) [Cost Band 8],
sum(invoice.costband9) [Cost Band 9], 
sum(invoice.costband10) [Cost Band 10], 
sum(invoice.costband11) [Cost Band 11], 
sum(invoice.costband12) [Cost Band 12], 
-- see changes in red in the next line
sum(isnull(invoice.costband1,0) + isnull(invoice.costband2,0)),
sum(invoice.triadmdchg) [TUOS], 
sum(invoice.misccost1) [Settl.& Agent],
invuos.costband1
from invoice, account, site, invuos
where
account.acct_link = invoice.acct_link
and
account.customer_link = 1037    
and
site.site_link = account.site_link
and
invoice.utilno = 1
and 
invoice.invoicedatefrom between '2010-04-01 00:00:00.000' and '2011-03-01 00:00:00.000'
and
account.acct_link = INVUOS.acct_link
and 
invoice.invdate = INVUOS.invdate
Group by site.Site_Name, account.mpan, account.Acctno
Order by account.Acctno

bd528
Starting Member

23 Posts

Posted - 2011-07-14 : 08:47:36

Now that the isnull line is written correctly, I get the permissions issue!

I think I may have to complete that part of the task in Excel, which is a pity.

Unless you have any other ideas, thanks so much for your time. I love the formatting of the code you've used. I'll be doing that in future!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-14 : 08:58:08

quote:
Only System Administrators are allowed to run scripts that modify the database

I don't think that this is a build in message coming from SQL Server.
Ask your dba what he has done.

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 09:03:48

I am using SoftTree SQL Assistant to do the formatting. I think there are free tools that do the formatting too. SSMS Tools Pack for one (http://www.ssmstoolspack.com/). Ssmstoolspack is written by a regular contributor to SQL Team.

It is a pity you have to rework and reproduce that query in Excel. It is precisely the type of thing that you are trying to do via this query that SQL excels at and Excel sucks (pardon my French). Before you go the Excel route, if I were you, I would speak to the DBA's or whomever can give you permissions to run this query on the Server.

bd528
Starting Member

23 Posts

Posted - 2011-07-14 : 09:19:25

Yes, I will try and resolve the permissions issue with admin. They can only say no...
Thanks for all the help - a great first impression for the site.

bd528
Starting Member

23 Posts

Posted - 2011-07-14 : 11:34:51

I completely re-wrote my query from scratch and included

sum(isnull(invoice.costband1,0) + isnull(invoice.costband2,0))

and for some reason it now works perfectly. Thanks again for all you help sunitabeck.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 12:40:39

Woohooo! I am glad!!

Although, if the same wasn't working earlier and now started working, that is a little bit scary.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources