Solved

Sum - Case when

Posted on 2008-10-22
11
284 Views
Last Modified: 2012-05-05
The results shown in the Excel file : text.xls

Have a table tbl_testingfreight :
The result is the first tab of the Excel sheet (Actual Data)
After running the below script I get the results showed in the second tab (Result based on the query)

I am trying to do a SQL case when , but not able to see the result as needed
The final result needs tto be as shown in the tab (Results required)

The conditions are:
when the VndrNum = 359958 and  OpCo = 177 for that SUVCVndrNum, ItemNum --------  need to sum up the [Freight Cost / Case ($)] jus t as highlighted.

How can i apply this condion in the below query:

select TOP 100 PERCENT

	d.OpCo

	,d.SUVCVndrNum

	,d.VndrNum	

	,ItemNum

	,sum(Cases) [Case Volume]	

	,sum(FOB) [COGS ($)]

	,sum(isnull(FrtPerCase, 0)*Cases) [Freight Cost ($)]

	,sum(isnull(LEIPerCase, 0)*Cases) [LEI ($)]

	,max(isnull(FrtPerCase, 0)) [Freight Cost / Case ($)]

	,max(isnull(LEIPerCase, 0)) [LEI ($) / Case]

	

from

	tbl_testingfreight d

	left outer join test.dbo.[OpCo Locations] o

		on d.OpCo  =  o.OpCo

	cross join (select distinct sourcinginitiative 

			from [freightPO-OPCO]) si

where

	SourcedCases  >  0
 

group by

	d.OpCo

	,d.SUVCVndrNum

	,d.VndrNum

	,ItemNum

order by

	d.OpCo

	,ItemNum

Open in new window

test.xls
0
Comment
Question by:mercybthomas74
  • 5
  • 3
  • 3
11 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 22781882
Hi,

I'm having difficulty following your logic.

But think that my attached query might be close.

Plese apply the table alias's consistently. They are applied to only half the columns. It helps with code review and maintenance if they are all applied.

Cheers
  David
select TOP 100 PERCENT

	d.OpCo

	,d.SUVCVndrNum

	,d.VndrNum	

	,ItemNum

	,sum(Cases) [Case Volume]	

	,sum(FOB) [COGS ($)]

	,sum(isnull(FrtPerCase, 0)*Cases) [Freight Cost ($)]

	,sum(isnull(LEIPerCase, 0)*Cases) [LEI ($)]

	,max(isnull(FrtPerCase, 0)) [Freight Cost / Case ($)]

	,max(isnull(LEIPerCase, 0)) [LEI ($) / Case]

	, case

	   when VndrNum = 359958 and  OpCo = 177  then 

	       (

	       select sum(isnull(FrtPerCase, 0)*Cases)

	       from

            	tbl_testingfreight di

            	left outer join test.dbo.[OpCo Locations] o

            		on d.OpCo  =  o.OpCo

            	cross join (select distinct sourcinginitiative

            			from [freightPO-OPCO]) si

            where

            	SourcedCases  >  0

            	and di.SUVCVndrNum = d.SUVCVndrNum

            	and di.ItemNum = d.ItemNum

	       )

	   else null

	end

	

from

	tbl_testingfreight d

	left outer join test.dbo.[OpCo Locations] o

		on d.OpCo  =  o.OpCo

	cross join (select distinct sourcinginitiative 

			from [freightPO-OPCO]) si

where

	SourcedCases  >  0

 

group by

	d.OpCo

	,d.SUVCVndrNum

	,d.VndrNum

	,ItemNum

order by

	d.OpCo

	,ItemNum

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22782044
Based on your XLS, it seems like you would be better off making a view out of your original query and then do something like this:

SELECT *,
CASE VndrNum WHEN '359958' THEN ISNULL((SELECT [Freight Cost / Case ($)] FROM yourView v2 WHERE v2.VndrNum = v1.SUVCVndrNum AND v2.ItemNum = v1.ItemNum AND v2.OpCo = 177), 0.0)
ELSE 0.0 END AS [Addtl Freight Cost / Case ($)]
FROM yourView v1
WHERE OpCo <> 177

Then add up [Addtl Freight Cost / Case ($)] AND [Freight Cost / Case ($)] in spreadsheet or in query.  Just simplified here, but if you type out columns in select you can just place this case statement after the original freight/case column as a + and add right then and there.
0
 

Author Comment

by:mercybthomas74
ID: 22788104
i have the code working but i see duplicates , something were i did the grouping, please help...
I took the d.SUVCVndrNum  d.VndrNum off
select TOP 100 PERCENT

	d.OpCo

	,max(o.OpCoName) OpCoName

	,ItemNum

	,max(Description) Description

	,sum(Cases) [Case Volume]

	,sum(Weight) [Weight Volume]		

	,sum(FOB) [COGS ($)]

	,sum(isnull(FrtPerCase, 0)*Cases) [Freight Cost ($)]

	,sum(isnull(LEIPerCase, 0)*Cases) [LEI ($)]	

	, (max(isnull(FrtPerCase, 0)) ) + (case when VndrNum = '359958'  then 

	       		isnull((select max(FrtPerCase)

	       			from SourcingFreightDetail_VndrNum di

            		      	where

            				di.SourcedCases  >  0

            				and di.SUVCVndrNum = d.SUVCVndrNum

            				and di.ItemNum = d.ItemNum  

					and di.opco='177'  

			      	

				group by di.SUVCVndrNum,di.ItemNum ,di.opco),0)

	else 0

	end )[Freight Cost / Case ($)]

	

	,max(isnull(LEIPerCase, 0)) [LEI ($) / Case]

	,max(si.SourcingInitiative) SourcingInitiative
 

from

	SourcingFreightDetail_VndrNum d

	left outer join test.dbo.[OpCo Locations] o

		on d.OpCo  =  o.OpCo

	cross join (select distinct sourcinginitiative 

			from [freightPO-OPCO]) si

where

	d.SourcedCases  >  0 and 

	d.opco <>'177' 

	

 

group by

	d.OpCo

	,ItemNum

	,d.SUVCVndrNum 

	,d.VndrNum
 

order by

	d.OpCo

	,ItemNum

Open in new window

test.xls
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22788229
Hi,

You have removed d.SUVCVndrNum and d.VndrNum from the column list, but not from the group by. Suggest that you take them out of the group by and that should remove your duplicates.

Cheers
  David
0
 

Author Comment

by:mercybthomas74
ID: 22788304
i tried that , since it is used the subquery its asking for those 2 fields to be grouped
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 35

Expert Comment

by:David Todd
ID: 22788385
Hi,

Okay, then you are going to see apparent duplicates. Since they are in the group by, removing them from the field list like you have shouldn't alter the results as such.

That is, I don't understand how removing them suddenly makes the query work.

Cheers
  David
0
 

Author Comment

by:mercybthomas74
ID: 22788393
the error i get is:

Column 'd.SUVCVndrNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'd.VndrNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 

Author Comment

by:mercybthomas74
ID: 22788570
how can i show one record, without having to add those 2 fields with total values,
I tried all ways either showing error or duplicates
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22789330
Have you completely tried my suggestion -- http:22782044?

It appears you are using pieces of it, but an important part of what I suggested was to make your original query into a view.  This is important as the subquery must exactly match the original query and you need to do the calculations / joins based on the already grouped and aliased data like it appears in your spreadsheet.  

If you are unwilling/able to use that approach, please advise and will go on from there but will probably need to be slightly adjusted as my solution was based on that fact.

Regards,
Kevin
0
 

Author Comment

by:mercybthomas74
ID: 22790324
mwvisa1: Actually I tried your suggention ,
I took off the d.SUVCVndrNum and d.VndrNum and thats when i get the error.
So i created the given script into a view and then sumed up the values in another view...
I guess that could be the best approach
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22790343
Yes, OR pull all the original data and extra column into Excel and SUM into Excel.  That way you only have the one view and your query to pull data in Excel does the additional logic.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now