Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL - Set "default" value?

Posted on 2004-11-08
6
Medium Priority
?
197 Views
Last Modified: 2008-03-03
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
0
Comment
Question by:gordonn
  • 2
4 Comments
 
LVL 10

Expert Comment

by:lengreen
ID: 12525528
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
 
LVL 9

Expert Comment

by:TriggerHappy
ID: 12526761
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
 

Author Comment

by:gordonn
ID: 12530990
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
 
LVL 9

Accepted Solution

by:
TriggerHappy earned 400 total points
ID: 12533647
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question