SQL - Set "default" value?

Hi there

I've got the following simple query

SELECT TABLEX.BuySell AS TradeType, Count(TABLEX.BuySell) AS [CountNum], Sum(TABLEX.Amount) AS [Sum]
FROM TABLEX
GROUP BY TABLEX.BuySell;

that returns the number of and the sum of "Buys" and "Sells" (TradeType column). If either (or both) "buy" or "sell" is equal to zero, the query returns a blank table.... so i was wondering if it's possible to set a "default" record with both "CountNum" and "Sum" equal to zero?

Thanks

neil
gordonnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lengreenCommented:
You Could do a

SELECT TABLEX.BuySell AS TradeType, Count(TABLEX.BuySell) AS [CountNum], Sum(TABLEX.Amount) AS [Sum]
FROM TABLEX
GROUP BY TABLEX.BuySell

union SELECT 'DEFAULT',0,0


not pretty & would be there even if there were other records


cheers

Len
0
TriggerHappyCommented:
Not quite sure if I understand the question correctly but perhaps simply changing the Sum field a bit.

Surround the TABLEX.Amount in your Sum with NZ() to convert all null values to 0.

So the resulting query would look like this:

SELECT TABLEX.BuySell AS TradeType, Count(TABLEX.BuySell) AS [CountNum], Sum(Nz(TABLEX.Amount)) AS [Sum]
FROM TABLEX
GROUP BY TABLEX.BuySell;

That will make sure that if you get a null value in Amount then it will convert that null to a 0 and thus be able to add it properly without killing the entire operation.

Hope that helps,
0
gordonnAuthor Commented:
hi guys

thanks, however, not exactly what i'm looking for... Let me try and re-explain:

Say I've got 2 queries, Q1 and Q2

Q1:
SELECT BuySell As TradeType, Count(BuySell) As [COUNT], Sum(Amount) As [SUM]
FROM TABLEX
WHERE User='Jack'
GROUP BY BuySell

Q2:
SELECT BuySell As TradeType, Count(BuySell) As [COUNT], Sum(Amount) As [SUM]
FROM TABLEX
WHERE User='Jill'
GROUP BY BuySell

Say the 2 queries have the following output:

Q1:
TradeType                     Count      Sum
Buy            8      1000
Sell            1      2000


Q2:
TradeType                     Count      Sum
Sell            4      500

I want to create a 3rd query to produce a table with the following output:

TradeType       TotCount        TotSum      Count_Jack                Sum_Jack       Count_Jill        Sum_Jill
Buy       8        1000      8            1000       0        0
Sell       5        2500      1            2000       4        500

Thanks



0
TriggerHappyCommented:
The problem with that is that SQL has no way of figuring out how many rows you have.  Have you considered a crosstab query?

This would replace both your queries...and allow you to select any employee you wish out of it with a where clause

TRANSFORM Sum(TABLEX.Amount) AS SumOfAmount
SELECT TABLEX.[User], Sum(IIf([BuySell]="Buy",1,0)) AS BuyTotal, sum(IIf([BuySell]="Sell",1,0)) AS sellTotal, Nz(Sum(TABLEX.Amount)) AS [Total Of Amount]
FROM TABLEX
GROUP BY TABLEX.[User]
PIVOT TABLEX.BuySell In ("Buy","Sell");
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.