SUM of rows, when selection returns no rows

[Shashi Sadasivan]

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
[CODE=sql]select sum(qty) from mytable where id = 1 and id1 = 1;[/CODE]

this returns NULL
my aim is to subtract a value as this is a subquery in another sql statement.
so if i do
[CODE=sql]select 20 - sum(qty) from mytable where id = 1 and id1 = 1;[/CODE]
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

[Shashi Sadasivan]

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

[Delerna]

Was just about to post the answer when I saw you already had the answer I was going to post

[ck9663]

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

[Shashi Sadasivan]

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

[CODE=sql]select
cost1 - (select IsNull(sum(valu e),0) from subTable where subTable.ID = myTable.ID) AS FORUMLA1, cost2 - (select IsNull(sum(valu e),0) from subTable where subTable.ID = myTable.ID) AS FORUMLA2
from myTable[/CODE]

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?

[ck9663]

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

[CODE=sql]select
cost1 - (select IsNull(sum(valu e),0) from subTable where subTable.ID = myTable.ID) AS FORUMLA1, cost2 - (select IsNull(sum(valu e),0) from subTable where subTable.ID = myTable.ID) AS FORUMLA2
from myTable[/CODE]

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?

select myTable.ID, cost1 = 20 - subtable.subtablesum, cost2 = 30 - subtable.subtablesum
from myTable
inner join 
   (select subTable.ID, IsNull(sum(value),0) as subtablesum from subTable group by subTable.ID) subtable on myTable.ID = subtable.ID

-- CK

[Delerna]

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
[code=sql]
Create proc pr_ProcedureToD emoVariables
DECLARE @Result int

set @Result=(select sum(qty) from mytable where id = 1 and id1 = 1;)


SELECT qty/@Result*100 as PercentOfTotal
FROM mytable

Go
[/code]

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.

[Shashi Sadasivan]

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
[code=sql]
Create proc pr_ProcedureToD emoVariables
DECLARE @Result int

set @Result=(select sum(qty) from mytable where id = 1 and id1 = 1;)


SELECT qty/@Result*100 as PercentOfTotal
FROM mytable

Go
[/code]

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 !