February 16, 2009 at 8:58 pm
I have a select query where I do 7 sums
The total number of rows in the query is not more than 150
All the rows exists in the table but I must do the sum only when row has a particular status(status column value = 1)
The problem is when there is no row with status = 1 all my sums return NULL but I need zero instead of NULL
I have two solutions
1 - Do the query with status = 1 and add the ISNULL funtion for each SUM
or
2 - Do the query without "Where status = 1"
In solution 1, I would do the sum on a part of the 150 rows (only those with status = 1) but I maybe have a overhead of 7 ISNULL. And all the ISNULL are there just in case the query returns no row. When there is at least one row with status = 1, ISNULL are in fact not necessary.
In solution 2, I would do the sum on all 150 rows, and then, the sums will work wihout any need of IsNULL because I know I will always SUM in a query returning rows.
I would like to get your opinion on which solution is the most standard. I know but works but I just want something clean so which solution should I use?
Thank you
Martin
February 16, 2009 at 9:55 pm
You could do a conditional check on the table prior to executing your aggregation query:
IF EXISTS (SELECT 1 FROM Table_Name WHERE status = 1)
SELECT DIM1,
DIM2, ...,DIMN,
SUM(MEASURE1),
SUM(MEASURE2),...,SUM(MEASURE7)
FROM FACT F JOIN
DIM D ON
F.FACT_DIM_FK = D.DIM_SK
WHERE F.STATUS = 1
ELSE
PRINT "HELLO WORLD"
My example uses a hypothetical Dimension and Fact table but I think you will get the drift. I think this would avoid having to use the ISNULL function. If the columns are supposed to be aggregated based upon a business rule (status =1) I think you should opt to explicitly code this rule into the aggregation T-SQL. Just my 2 cents. I hope this helps.
Ken
February 16, 2009 at 10:06 pm
Please substitute "FACT" for "Table_Name" in the example I posted. Oops.
Ken
February 17, 2009 at 6:30 am
I agree with Ken that his solution is probably a better way to handle the situation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply