Having SUM() trouble... - SQL Server Forums

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

Having SUM() trouble...

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-12-02 : 10:00:52

I am trying to SUM() all of a specific class of material issued or returned from all jobs over a given date range. While the issues and returns are all stored in the same table, the transactions are tagged as either 'To A Job' or 'From A Job' and share a Quantity column (- for issues, + for returns)...

The table would look something like this to show issues and returns...


PARTNO		REVNO		FROMJOB		TOJOB		QTY		TIMESTAMP
WIDGET1		00				JO100		-300		2008-10-11 23:22:22.000
SPROCKET	00				JO100		-10		2008-10-11 23:22:44.000
WIDGET1		00				JO200		-100		2008-10-11 23:30:51.000
WIDGET3		01				JO200		-1000		2008-10-11 23:31:01.000
WIDGET1		00				JO300		-600		2008-10-11 23:50:08.000
...
WIDGET3		01		JO200				500		2008-10-31 17:08:01.000
WIDGET1		00				JO200		-400		2008-10-31 17:10:15.000
WIDGET1		00		JO300				200		2008-10-31 17:20:11.000

My stab at a SELECT...


SELECT 
	??? AS JOB_NUMBER,
	PARTNO,
	REVNO,
	SUM??? AS TOTAL_ISSUED
WHERE
	TIMESTAMP BETWEEN @FromDate AND @ToDate
	AND
	PARTNO LIKE ('WIDGET%')
GROUP BY
	??? (JOB_NUMBER)

And the desired result:


JOB_NUMBER		PARTNO		REVNO		TOTAL_ISSUED
JO100			WIDGET1		00		-300
JO200			WIDGET1		00		-500
JO200			WIDGET3		01		-500
JO300			WIDGET1		00		-400

How would I go about SUM()ing the QTY field when sometimes there will be no returns? And what would I use as the GROUP BY? My problem is further compounded by the fact that this information would be used as a subquery to a larger query spread across two databases. (Long story, headache in process)...

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 10:06:24

[code]SELECT ISNULL(NULLIF(FROMJOB,' '),TOJOB),PARTNO,REVNO,
SUM(QTY)
FROM Table
GROUP BY ISNULL(NULLIF(FROMJOB,' '),TOJOB),PARTNO,REVNO
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 10:07:28

[code]SELECT COALESCE(NULLIF(toJob, ''), NULLIF(fromJob, '')) AS jobNum,
partNo,
revNo,
SUM(qty)
FROM Table1
WHERE timeStamp BETWEEN @fromDate AND @toDate
AND partNo LIKE '%widget%'
GROUP BY COALESCE(NULLIF(toJob, ''), NULLIF(fromJob, '')),
partNo,
revNo[/code]Use COALESCE if possible.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 10:12:19

i thought ToJob,FromJob will be having same datatype seeing the values. thats why i used ISNULL. But, as peso suggests most cases its better to use COALESCE instead of ISNULL

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/04/isnull-or-coalesce.aspx

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-12-02 : 10:28:24

Wow, you guys are fast!

I tried Peso's suggestion, and I'll have to look at the underlying data to see if the SUMs are correct, but it seemed to cure the problem of having NULLs in the Qty field. Only thing is, there are many entries with a 'NULL' jobNum, which are probably due to issues and returns that weren't associated with a job number, (i.e. recycling, etc) it's something I'll have to look at the underlying info to figure out & exclude from the select.

But thanks a bunch for hammering that out so quickly; I take it this isn't an uncommon problem? How do you folks figure out stuff like COALESCE? I have the BOL but apparently have never stumbled into that section. Very cool.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 10:39:29

If your sample data is incorrect, that is the missing data are not empty space, but instead NULL, you can simplify the SELECT as

SELECT		COALESCE(toJob, fromJob) AS jobNum,
		partNo,
		revNo,
		SUM(COALESCE(qty, 0))
FROM		Table1
WHERE		timeStamp BETWEEN @fromDate AND @toDate
		AND partNo LIKE '%widget%'
GROUP BY	COALESCE(toJob, fromJob),
		partNo,
		revNo

E 12°55'05.63"
N 56°04'39.26"

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-12-02 : 10:57:07

Thanks for the clarification, although in this case, the empty fields are actually '' and not NULL. It turns out, the NULL job numbers are related to cases where raw material is newly injected into the inventory system (either through bulk purchase or recycling) in which case there is no tojob/fromjob, but one of another dozen cryptic from/to columns.

Thanks again for your help, I'd still be going in circles without it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources