Is this possible? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Is this possible?

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-23 : 11:57:00

Hi, I've got a query which finds a particular customer and sums her credit and debit values as shown below:

SELECT
CLIENT.TORO_CLIENTID,
CLIENT.FIRST_NAME,
CLIENT.TITLE,
CLIENT.LAST_NAME,
SUM(TRANSACTIONS.CREDIT) AS FIELD_1,
SUM(TRANSACTIONS.DEBIT) AS FIELD_2
FROM
CLIENT
INNER JOIN TRANSACTIONS ON (CLIENT.TORO_CLIENTID = TRANSACTIONS.TORO_CLIENTID)
WHERE
client.toro_clientid = 7710112
GROUP BY
CLIENT.TORO_CLIENTID,
CLIENT.FIRST_NAME,
CLIENT.TITLE,
CLIENT.LAST_NAME

Is it possible to reference the results from the above two sum calculations in such a way that you can show the difference between the two i.e sum(transactions.credit) - sum(transactions.debit)?

Any help would be much appreciated.

Cheers

Paul

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-23 : 12:08:24

Yes, you can just write an expression that does it:

SELECT
CLIENT.TORO_CLIENTID,
CLIENT.FIRST_NAME,
CLIENT.TITLE,
CLIENT.LAST_NAME,
SUM(TRANSACTIONS.CREDIT) AS FIELD_1,
SUM(TRANSACTIONS.DEBIT) AS FIELD_2,
SUM(TRANSACTIONS.CREDIT) - SUM(TRANSACTIONS.DEBIT) AS Difference
FROM
CLIENT
INNER JOIN TRANSACTIONS ON (CLIENT.TORO_CLIENTID = TRANSACTIONS.TORO_CLIENTID)
WHERE
client.toro_clientid = 7710112
GROUP BY
CLIENT.TORO_CLIENTID,
CLIENT.FIRST_NAME,
CLIENT.TITLE,
CLIENT.LAST_NAME

In SQL Server, you can't use the column aliases in that kind of expression though (Field_1-Field_2 won't work), however other database products may let you do so.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-23 : 12:08:29

didn't you answer your own question?

SUM(TRANSACTIONS.CREDIT) - SUM(TRANSACTIONS.DEBIT) AS DIFF

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-23 : 12:13:46

Sort of, but now it seems much clearer:
I didn't know that you could carry out a calculation and then add the "As ..." on the end to display the result.
As a side point could I then insert the result of:

SUM(TRANSACTIONS.CREDIT) - SUM(TRANSACTIONS.DEBIT) AS Difference

nto a field of a table?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-23 : 12:15:09

Sure, as long as there is a column in the table to take the result.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-23 : 23:54:14

quote:

I didn't know that you could carry out a calculation and then add the "As ..." on the end to display the result.

You don't need the As it allows you to give the column a name.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-24 : 04:18:08

Thankyou!

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-24 : 06:45:42

Sorry to ask more questions but, if I do the following calculation

SUM(TRANSACTIONS.CREDIT) - SUM(TRANSACTIONS.DEBIT) AS Difference

do I need to do an update query to get this into another table.
N.B the result will be stored in a customers table and not the transactions table referenced above. Therefore how do I incorporate the two tables in the update query?

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-24 : 07:21:39

Hi,

I think I'm nearly there! My query is as follows:

update client
set field_1 =
(select sum(transactions.credit)
from CLIENT
INNER JOIN TRANSACTIONS ON (CLIENT.TORO_CLIENTID = TRANSACTIONS.TORO_CLIENTID)
group by
client.toro_clientid)

The only problem is that when I run it I get the following error message:

multiple rows in singleton select

Can anyone help?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-24 : 16:40:25

First read this
http://www.sqlteam.com/item.asp?ItemID=3876

select sum(transactions.credit)
from CLIENT
INNER JOIN TRANSACTIONS ON (CLIENT.TORO_CLIENTID = TRANSACTIONS.TORO_CLIENTID)
group by
client.toro_clientid

This returns a set of records ex:
10
20
50

WHAT you really want is an update based on a join

UPDATE CLIENT
SET field_1 = SUM(B.credit)
FROM
CLIENT A
INNER JOIN TRANSACTIONS B ON A.TORO_CLIENTID = B.TORO_CLIENTID
GROUP BY A.toro_clientid

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-24 : 17:21:17

Hi ValterBorges,

Thanks for posting! I tried your query but when I compile it I get the following error message:

Invalid Token
FROM

This may be because I'm using Interbase not sql server. What do you think?

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-01-24 : 17:39:00

Probably...

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2003-01-25 : 12:56:39

I don't know Interbase, but the following may work

update client as c
set field_1 =
(select sum(transactions.credit)
from CLIENT
INNER JOIN TRANSACTIONS ON (CLIENT.TORO_CLIENTID = TRANSACTIONS.TORO_CLIENTID)
where client.clientid = c.clientid)

(it's almost ANSI sql 99 apart from the use of a correlation name for the update table.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-25 : 15:13:20

quote:

This may be because I'm using Interbase not sql server. What do you think?

Maybe these guys can help you.
http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/27/pid/756

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-01-27 : 04:21:11

Hi, LarsG query works, I just had to take the "as c" part off the update statement. The only problem is that when I run the query it takes in excess of 25 minutes to run. There is approx 12000 records in the client table and there are 6000 records in the transactions table.
Is there any other way or writing this so that it will run quicker?
I have got 65 different datassets to run this on so you can see why I need it to run faster.

Best Regards

Paul Rowling

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-27 : 08:47:41

Which statement did you end up with was it Lars's version?

You might want to create a temp table with id and sum and then do the update based on that table.

update client as c
set field_1 =
(SELECT sumcredit from temptable t where c.id = t.id)

Are you indexing id?

Edited by - ValterBorges on 01/27/2003 08:55:39

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources