Query & sub query

Adam H-W · March 11th, 2004, 08:45 AM

Hi there

I've got 2 select statements that I want to put into 1 statement to output a total turnover figure though I'm not sure how to do it.

Here are my queries:

SELECT SUM(t.Price * t.Quantity) AS 'Total Turnover' FROM TransactionLogItem t WHERE t.TransactionID LIKE 'SHHC_%'

SELECT SUM(PP) FROM TransactionLogItem t WHERE t.TransactionID LIKE 'SHHC_%'

and I need to put them together as one

many thanks

Adam

Jeff Mason · March 11th, 2004, 09:44 AM

Maybe I don't understand what you are trying to do, but why not simply:

Code:

SELECT SUM(t.Price * t.Quantity) AS 'Total Turnover', SUM(PP) AS whatever
FROM TransactionLogItem t WHERE t.TransactionID LIKE 'SHHC_%

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Adam H-W · March 11th, 2004, 09:51 AM

Hi Jeff,

thanks for your swift response. sorry, I don't think I explained it clearly.

I need the output to be a single total value rather than 2 total values

thanks

Adam

Jeff Mason · March 11th, 2004, 02:57 PM

So your resultset is a single number which is the sum of the two? Try:

Code:

SELECT SUM(t.Price * t.Quantity) + SUM(PP) AS whatever
FROM TransactionLogItem t WHERE t.TransactionID LIKE 'SHHC_%

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com