Showing posts with label sums. Show all posts
Showing posts with label sums. Show all posts

Wednesday, March 28, 2012

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.

Friday, March 23, 2012

making a statistics table....

hi. i'm trying to make a report in asp that sums up a whole bunch of statistics. the report is quite lengthy and takes about 2 minutes to load. i thought i would solve that problem by making a table to dump the statistics into instead of calculating the statistics every time the report is viewed. this way, the page would just read the table with the statistics already put in it. these statistics would be updated every day or two so i don't need anything up to the minute.

however, i have run into a small problem which is actually updating the information in the table. my new table is called OfficeReport and it looks like this:

UserID Stat1 Stat2 Stat3 ..... Stat32
23 0 0 0 0
56 0 0 0 0
72 0 0 0 0

this is a query for one of the statistics i am currently using:

SELECT DefendantCase.UserID, COUNT(DefendantCase.UserID) AS CountOfUserID
FROM DefendantCase LEFT JOIN UserDescription ON DefendantCase.UserID=UserDescription.UserID
WHERE UserDescription.Status=1 AND UserDescription.UserType=1
GROUP BY DefendantCase.UserID

if i run this query, this is what i get:

UserID CountOfUserID
54 21
60 10
52 29
4 4
27 22

how can i modify this query so its output updates the same UserID column in the OfficeReport table? Thanks!I'm guessing you want to update when the userid is already present in the table and insert when it's not...
-- Insert
-- OUTER JOIN with OfficeReport WHERE UserID IS NULL should give all
-- UserID's that are not in OfficeReport
INSERT OfficeReport (UserID, Stat1)
SELECT dc.UserID
,COUNT(dc.UserID)
FROM DefendantCase dc
LEFT OUTER JOIN UserDescription ud ON dc.UserID = ud.UserID
LEFT OUTER JOIN OfficeReport orep ON orep.UserID = dc.Userid
WHERE ud.Status = 1
AND orep.UserID IS NULL

-- Update
UPDATE orep
SET Stat1 = COUNT(dc.UserID)
FROM OfficeReport orep
INNER JOIN DefendantCase dc ON dc.UserID = orep.UserID
LEFT OUTER JOIN UserDescription ud ON dc.UserID= ud.UserID
WHERE ud.status = 1|||if i use your update method, i get this error:
An aggregate may not appear in the set list of an UPDATE statement. thanks!|||Aaah, yes, an aggregate (COUNT in this case) skips NULL columns.

Sorry, I seem to have forgotten the GROUP BY in the INSERT en UPDATE this should be added to both:
GROUP BY dc.UserID
:osql