Removing (or rather, showing) null results

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

General SQL Server Forums

New to SQL Server Programming

Removing (or rather, showing) null results

Author

Topic

sierra
Starting Member

9 Posts

Posted - 2010-01-19 : 12:08:35

Hi all

The structure of a table I need to query is such that I get null results, which I want to avoid because I'm placing the data in an Excel spreadsheet and the results need to stay in the same cells each time.

The query is something like this:

SELECT
ISNULL(Sum(chargetype1),0)
+ ISNULL(Sum(chargetype2),0)
+ ISNULL(Sum(chargetype3),0)
+ ISNULL(Sum(chargetype4),0)
+ ISNULL(Sum(chargetype5),0)
+ ISNULL(Sum(chargetype6),0)
- ISNULL(Sum(costtype1),0) AS Total

FROM revenue

GROUP BY CompanyID, SalesCode

There are 5 different SalesCode types and several different CompanyID's, but the usage of SalesCodes changes sometimes so at a given time there might be two companies that have used all of the SalesCodes, but others who have not.

What I need to get from the query is the Total as calculated above, but for every SalesCode for every CompanyID (zero, if there are no results for a given SalesCode and CompanyID combination).

I've tried doing:

SELECT
ISNULL(Sum(chargetype1),0)
+ ISNULL(Sum(chargetype2),0)
+ ISNULL(Sum(chargetype3),0)
+ ISNULL(Sum(chargetype4),0)
+ ISNULL(Sum(chargetype5),0)
+ ISNULL(Sum(chargetype6),0)
- ISNULL(Sum(costtype1),0) AS Total

FROM revenue

WHERE (SalesCode='A' OR SalesCode='B' OR SalesCode='C' OR SalesCode='D' OR SalesCode='E')

AND (CompanyID='1' OR CompanyID='2' etc etc)

GROUP BY CompanyID, SalesCode

... but because some SalesCodes do not exist for some CompanyID's I still get differing numbers of rows for each CompanyID.

Can anyone help?

Many thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-19 : 12:19:50

Sure...do you have table of all valid values?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sierra
Starting Member

9 Posts

Posted - 2010-01-19 : 12:25:16

It's huge, so not easily. If we assume there are 5 SalesCodes (A thru E) and 20 CompanyID's (1 thru 20), and the 6 chargetypes and 1 costtype as indicated, is that enough information? I can rebuild the query based on that; the actual table contains a great deal of data - I've simplified the query right down to the areas I need assistance with.

Thank you for your time.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-19 : 12:32:32

you don't even have company code in you select list...so how do you know...is that all of the sql?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sierra
Starting Member

9 Posts

Posted - 2010-01-19 : 12:39:16

Good point. Editing-up simpler queries isn't always the best way to demonstrate, I know! I'm more of a PHP+MySQL programmer; I don't often work in Excel with MS SQL so this is a new area for me. Basically I want to get all the totals for all the salescodes for all the companies.

A result like:

Company / SC / Total
1 A 1000
1 B 2000
1 C 1000
1 D 0 (no entries for Company 1 and SalesCode D so return a zero)
1 E 1000
2 A 1000
2 B 0 (no entries, as above)
2 C 4000
2 D 1000
2 E 5000

A fuller query is:

SELECT CompanyID, SalesCode,
ISNULL(Sum(chargetype1),0)
+ ISNULL(Sum(chargetype2),0)
+ ISNULL(Sum(chargetype3),0)
+ ISNULL(Sum(chargetype4),0)
+ ISNULL(Sum(chargetype5),0)
+ ISNULL(Sum(chargetype6),0)
- ISNULL(Sum(costtype1),0) AS Total

FROM revenue

GROUP BY CompanyID, SalesCode

Thanks again.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-19 : 12:42:32

ok, so do ou have a table of all distinct company/sales codes?

If you do, select from <that table> LEFT JOIN Revenue

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sierra
Starting Member

9 Posts

Posted - 2010-01-19 : 15:38:12

I don't know if there are tables containing the company & sales codes because this is the back end to some software I didn't write, but I'll certainly take a look! I could even add the relevant tables then reference them.

If this isn't possible (and I can see it's a really simple, neat way to do it) are there any other suggestions?

Thanks

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-20 : 02:36:02

Hi sierra,

Try the following and let me know if this is what you want.

SELECT CompanyID, SalesCode,
Sum(ISNULL(chargetype1,0))
+ Sum(ISNULL(chargetype2),0))
+ Sum(ISNULL(chargetype3),0))
+ Sum(ISNULL(chargetype4),0))
+ Sum(ISNULL(chargetype5),0))
+ Sum(ISNULL(chargetype6),0))
- Sum(ISNULL(costtype1),0) AS Total

The difference is that Isnull(sum) will sum up the null values, and give you nothing (you can't sum up null), whereas Sum(isnull(value, 0)) will sum up the 0s and return 0 ... :)

Hope this helps,

eugene

sierra
Starting Member

9 Posts

Posted - 2010-01-20 : 09:35:27

Thanks Eugene

That doesn't seem to make any difference but I did try it. I need rows returned from the revenue table for all SalesCodes even if there are no rows for a given SalesCode. I haven't been able to make it work with LEFT JOIN either, although I do have a table called SalesCodes that contains the column SalesCode which relates to the SalesCode column in the revenue table, so I thought I could follow Brett's suggestion but that doesn't work either.

To re-iterate, what I'm trying to do is produce a list of ALL SalesCode's from the SalesCode table, then for each SalesCode, the sum of the various chargetypes that exist for that SalesCode in the revenue table (and then sorted by CompanyID - another column that exists in the revenue table).

I think maybe my ISNULL's are in the wrong place; it's as if the TOTAL needs to be ISNULLed... but I don't really know how to express it. Almost two days solid on this - I am continuing to read up on all suggestions so all input is welcome.

Thanks again

sierra
Starting Member

9 Posts

Posted - 2010-01-20 : 10:17:47

Here's the latest attempt which is nearly there.

SELECT salescodes.SalesCode, revenue.CompanyID,

Sum(ISNULL(chargetype1,0))+Sum(ISNULL(chargetype2,0))+Sum(ISNULL(chargetype3,0))+Sum(ISNULL(chargetype4,0))+Sum(ISNULL(chargetype5,0))+Sum(ISNULL(chargetype6,0))-Sum(ISNULL(costtype1,0)) AS 'Total'

FROM revenue

LEFT OUTER JOIN salescodes

ON (salescodes.SalesCode = revenue.SalesCode)
WHERE (revenue.PeriodDate Between '2010-01-01' And '2010-03-31') AND (revenue.type='0')
GROUP BY salescodes.SalesCode, revenue.CompanyID
HAVING (revenue.CompanyID In ('1','2','3','4','5'))
ORDER BY salescodes.SalesCode, revenue.CompanyID
_____________________________

This still doesn't show me all the SalesCodes; it only shows the SalesCode IF there is a total for that SalesCode in the revenue table. I think I can see why but I don't know how to change the query to give me the data I want!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 06:09:10

Hi Sierra,

You descriptions aren't very clear but I believe that this may be what you want. (Or at least closer to it).

IF this isn't right then what you will have to to do is provide us with

1) Table structures for the tables involved
2) sample data
3) required output from that data

But here's my stab at your requirement



SELECT
	s.[salesCode]
	, r.[companyID]

	, [total] =
		r.[charge1]
		+ r.[charge2]
		+ r.[charge3]
		+ r.[charge4]
		+ r.[charge5]
		+ r.[charge6]
		- r.[cost1]

FROM
	salesCodes s

	LEFT JOIN (
		SELECT
			[salesCode] AS [salesCode]
			, [companyID] AS [companyID]
			, SUM(ISNULL([chargetype1], 0)) AS [charge1]
			, SUM(ISNULL([chargetype2], 0)) AS [charge2]
			, SUM(ISNULL([chargetype3], 0)) AS [charge3]
			, SUM(ISNULL([chargetype4], 0)) AS [charge4]
			, SUM(ISNULL([chargetype5], 0)) AS [charge5]
			, SUM(ISNULL([chargetype6], 0)) AS [charge6]
			, SUM(ISNULL([costtype1], 0)) AS [cost1]
		FROM
			revenue
		WHERE
			[companyId] IN ('1', '2', '3', '4', '5')
		GROUP BY
			[salesCode]
			, [companyID]
		)
		r ON r.[salesCode] = s.[salesCode]

Good luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sierra
Starting Member

9 Posts

Posted - 2010-01-21 : 07:17:52

That's really, really close to what I want - thank you very much!

What I get from that is every salescode, with the total revenue for that code, for each company that used it.

ALMOST perfect, except when a salescodeID is only used by 2 companys in the revenue table, the salescode only appears twice (once for each company) in the result.

If a salescodeID is not used by any companys in the revenue table, the salescode appears once (which is better than not at all, which is what I was getting before!)

I need it to appear for every companyID regardless of whether they used it or not.

There are 5 companyID's in use and 20 salesCodeID's in the SalesCode table, so I'd like the result to be 100 rows long (each salesCode listed 5 times - once for each station), grouped like:


SC  CO   Total
1   A    0
1   B    25000
1   C    17000
1   D    0
1   E    500
2   A    100
2   B    0
2   C    0
2   D    5000
2   E    23000
... etc

Does that make sense?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 08:09:59

OK.


SELECT
	s.[salesCode]
	, c.[companyID]

	, [total] =
		ISNULL(
			r.[charge1]
			+ r.[charge2]
			+ r.[charge3]
			+ r.[charge4]
			+ r.[charge5]
			+ r.[charge6]
			- r.[cost1]
		, 0)

FROM
	salesCodes s

	JOIN company c ON c.[companyID] IN ('1', '2', '3', '4', '5')

	LEFT JOIN (
		SELECT
			[salesCode] AS [salesCode]
			, [companyID] AS [companyID]
			, SUM(ISNULL([chargetype1], 0)) AS [charge1]
			, SUM(ISNULL([chargetype2], 0)) AS [charge2]
			, SUM(ISNULL([chargetype3], 0)) AS [charge3]
			, SUM(ISNULL([chargetype4], 0)) AS [charge4]
			, SUM(ISNULL([chargetype5], 0)) AS [charge5]
			, SUM(ISNULL([chargetype6], 0)) AS [charge6]
			, SUM(ISNULL([costtype1], 0)) AS [cost1]
		FROM
			revenue
		WHERE
			[companyId] IN ('1', '2', '3', '4', '5')
		GROUP BY
			[salesCode]
			, [companyID]
		)
		r ON
			r.[salesCode] = s.[salesCode]
			AND r.[companyId] = c.[companyId]

Here I'm taking a stab in the dark that you have a company table and that it has a column called [companyID] (I'm assuming this from the naming convention of your revenue table).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sierra
Starting Member

9 Posts

Posted - 2010-01-21 : 08:33:45

Trying it now !

sierra
Starting Member

9 Posts

Posted - 2010-01-21 : 09:11:11

Excellent. That works really well; I added ORDER BY s.[salesCode], c.[companyId] at the end too.

Thank you, it's great to be able to tap into such expertise!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 10:10:46

You're welcome.

All the best

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources