making two sums in one query

Hi

Im new in this forum so don't if this is the right place to post these kind of questions..

Im using vb2005 and have made a program that deals with customers and payments.

I have a table called acount. Roughly it contains these columns

Number, amount, type..

type can be a payment or a charge

I would like to have a query that gives me this result:

Number, sum(payments), sum(charges) (grouped by number)

I think it is some type of merging these to querys

select number, sum(amount) where type = 0 group by number

and

select number, sum(amount) where type <> 0 group by number

Im using it to calculate a customers acount to see if they have paid to much or to little.

Any ideas?

Something like this

select number,
sum(case when type=0 then amount else 0 end) as payments,
sum(case when type<>0 then amount else 0 end) as charges
from mytable
group by number

|||

i just tried that, but get some kind of error from vbs error handling.. Its danish so the number is called nr and acount called konto. this is what i typped:

select nr,sum(case when type=0 then amount else 0 end) as pay, sum(case when type<>0 then amount else 0 end) as charges from konto group by nr

Can it have something to do with that ive tried it on an access database?

The program should be able to use both access and sql server..

|||Access doesn't support CASE expression so you cannot use the same SELECT statement if you want to run against Access & SQL Server. You have to use IIF in Access instead of CASE. Otherwise, the rest of the SELECT statement syntax should be fine. Alternatively, you can create a view in SQL Server and Access that contains the database specific query and query the view from the client side.|||

?v..

I cant get the iif sentence to work either.. Is this the query?

select nr,sum(iif when type=0 then amount else 0 end) as pay, sum(iif when type<>0 then amount else 0 end) as charges from konto group by nr

Am i missing something in the query?

|||

Please check the Access documentation for syntax on how to use IIF. It should be:

select nr,sum(iif(type=0, amount, 0)) as pay, sum(iif(type<>0, amount, 0)) as charges from konto group by nr

Also, this newsgroup is for SQL Server questions so please post only TSQL related questions or unless you are using SQL Server in some form or another. You need to post Access questions in the Access newsgroups to get better response.