SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Removing (or rather, showing) null results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sierra
Starting Member

9 Posts

Posted - 01/19/2010 :  12:08:35  Show Profile  Reply with Quote
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

Edited by - sierra on 01/20/2010 10:04:48

X002548
Not Just a Number

15586 Posts

Posted - 01/19/2010 :  12:19:50  Show Profile  Reply with Quote
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



Go to Top of Page

sierra
Starting Member

9 Posts

Posted - 01/19/2010 :  12:25:16  Show Profile  Reply with Quote
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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/19/2010 :  12:32:32  Show Profile  Reply with Quote
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



Go to Top of Page

sierra
Starting Member

9 Posts

Posted - 01/19/2010 :  12:39:16  Show Profile  Reply with Quote
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.

Edited by - sierra on 01/19/2010 12:39:55
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/19/2010 :  12:42:32  Show Profile  Reply with Quote
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



Go to Top of Page

sierra
Starting Member

9 Posts

Posted - 01/19/2010 :  15:38:12  Show Profile  Reply with Quote
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
Go to Top of Page

EugeneLim11
Posting Yak Master

167 Posts

Posted - 01/20/2010 :  02:36:02  Show Profile  Visit EugeneLim11's Homepage  Reply with Quote
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
Go to Top of Page

sierra
Starting Member

9 Posts

Posted - 01/20/2010 :  09:35:27  Show Profile  Reply with Quote
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
Go to Top of Page

sierra
Starting Member

9 Posts

Posted - 01/20/2010 :  10:17:47  Show Profile  Reply with Quote
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!

Edited by - sierra on 01/20/2010 11:50:46
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  06:09:10  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

Edited by - Transact Charlie on 01/21/2010 06:09:52
Go to Top of Page

sierra
Starting Member

9 Posts

Posted - 01/21/2010 :  07:17:52  Show Profile  Reply with Quote
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?



Edited by - sierra on 01/21/2010 07:20:48
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  08:09:59  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

sierra
Starting Member

9 Posts

Posted - 01/21/2010 :  08:33:45  Show Profile  Reply with Quote
Trying it now !
Go to Top of Page

sierra
Starting Member

9 Posts

Posted - 01/21/2010 :  09:11:11  Show Profile  Reply with Quote
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!
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  10:10:46  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
You're welcome.

All the best


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000