By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

SUM of rows, when selection returns no rows

Shashi Sadasivan
Expert 100+
P: 1,435
Hi,
I have a table with no rows for the moment.
table name = mytable
rows = id, id1, qty
id and id1 together is the primary key

when i try tthe following
Expand|Select|Wrap|Line Numbers
  1. select sum(qty) from mytable where id = 1 and id1 = 1;
this returns NULL
my aim is to subtract a value as this is a subquery in another sql statement.
so if i do
Expand|Select|Wrap|Line Numbers
  1. select 20 - sum(qty) from mytable where id = 1 and id1 = 1;
it return null, instead of 20.since the table is empty, there are no rows returned for id = 1, id1 = 1;

I tried using sum(case qty when NULL then 0 else qty) but still returns null.

any workarounds to this?
thankyou
May 13 '08 #1
Share this Question
Share on Google+
7 Replies


Shashi Sadasivan
Expert 100+
P: 1,435
20 minutes of search engine and without any result i had to post it here..
and the very next search i make, i get it.
isint that lovely

anyways this is how i have done it

select 20 - IsNull(sum(qty),0) from mytable where id = 1 and id1 = 1;

that returns 20


Though if you may have a way which you think is better, i would be glad to use that.
thanks
May 13 '08 #2

Delerna
Expert 100+
P: 1,134
Was just about to post the answer when I saw you already had the answer I was going to post
May 13 '08 #3

ck9663
Expert 2.5K+
P: 2,878
I think that's the best solution so far, rather than using a variable, unless you're going to need the value further down your code.

-- CK
May 13 '08 #4

Shashi Sadasivan
Expert 100+
P: 1,435
I think that's the best solution so far, rather than using a variable, unless you're going to need the value further down your code.

-- CK
Well, I would be interested to know how this is done.
So if i have to first subtract that value by 20 and then by 30 for each value obtained from another row

Expand|Select|Wrap|Line Numbers
  1. select 
  2. cost1 - (select IsNull(sum(value),0) from subTable where subTable.ID = myTable.ID) AS FORUMLA1, cost2 - (select IsNull(sum(value),0) from subTable where subTable.ID = myTable.ID) AS FORUMLA2
  3. from myTable
in this case i run the same query twice which is an utter waste of space, and decreases redability (but increases my chances to stay at that job)
So is there a way then to store it to a field and use it later on?
May 14 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Well, I would be interested to know how this is done.
So if i have to first subtract that value by 20 and then by 30 for each value obtained from another row

Expand|Select|Wrap|Line Numbers
  1. select 
  2. cost1 - (select IsNull(sum(value),0) from subTable where subTable.ID = myTable.ID) AS FORUMLA1, cost2 - (select IsNull(sum(value),0) from subTable where subTable.ID = myTable.ID) AS FORUMLA2
  3. from myTable
in this case i run the same query twice which is an utter waste of space, and decreases redability (but increases my chances to stay at that job)
So is there a way then to store it to a field and use it later on?

Will a join with subquery work?

Expand|Select|Wrap|Line Numbers
  1. select myTable.ID, cost1 = 20 - subtable.subtablesum, cost2 = 30 - subtable.subtablesum
  2. from myTable
  3. inner join 
  4.    (select subTable.ID, IsNull(sum(value),0) as subtablesum from subTable group by subTable.ID) subtable on myTable.ID = subtable.ID
-- CK
May 14 '08 #6

Delerna
Expert 100+
P: 1,134
Shashi, When using stored procedures and UDF's you can save the reult of a query into a variable and then use that variable within another query.
For example
Expand|Select|Wrap|Line Numbers
  1. Create proc pr_ProcedureToDemoVariables
  2.    DECLARE @Result int
  3.  
  4.    set @Result=(select sum(qty) from mytable where id = 1 and id1 = 1;)
  5.  
  6.  
  7.    SELECT qty/@Result*100 as PercentOfTotal
  8.    FROM mytable
  9.  
  10.    Go
  11.  
I believe that is the sort of thing ck was referring to.

If you wanted to get a calculated value and use that calculated calue elsewhere within the same query then one way would be with sub queries, as ck demonstrates in the previous post.
May 14 '08 #7

Shashi Sadasivan
Expert 100+
P: 1,435
Shashi, When using stored procedures and UDF's you can save the reult of a query into a variable and then use that variable within another query.
For example
Expand|Select|Wrap|Line Numbers
  1. Create proc pr_ProcedureToDemoVariables
  2.    DECLARE @Result int
  3.  
  4.    set @Result=(select sum(qty) from mytable where id = 1 and id1 = 1;)
  5.  
  6.  
  7.    SELECT qty/@Result*100 as PercentOfTotal
  8.    FROM mytable
  9.  
  10.    Go
  11.  
I believe that is the sort of thing ck was referring to.

If you wanted to get a calculated value and use that calculated calue elsewhere within the same query then one way would be with sub queries, as ck demonstrates in the previous post.

Sure...
Why didnt I think of that.
Well as they say, there is always a different way to do things.
MIne was a few costs extra. (infact double)

Thankyou for the Tip(s).

Thankyou ck !
May 14 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.