We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Nested CASE statement with aggregate function

Medium Priority
876 Views
Last Modified: 2013-11-07
I am looking for a way to make the following select statement work.  The problem is that I cannot use the field invno because I get the following error "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"  Is there any other way to write this?

The first select state works fine.  However, in this case I need to look at the first character of the invno to determine the company and that will then be the first part of getting the LEVEL.

This works perfectly
 
select Specno + '-' + spcompany + ' / ' + Project + ' / ' + spcontact as Order1, Count(*) as OrderCount, 
SUM(QtyShp) AS TotQty, Level=
CASE
	   	WHEN SUM(QtyShp) < 250 THEN 'A'
           	WHEN SUM(QtyShp) < 500 THEN 'B'
	   	WHEN SUM(QtyShp) < 1000 THEN 'C'
	   	WHEN SUM(QtyShp) < 5000 THEN 'D'
	   	WHEN SUM(QtyShp) < 10000 THEN 'E'
	   	ELSE 'F'
	   END 
From SBTInvoice 
where salesmn = 'aparker' 
Group by Specno + '-' + spcompany + ' / ' + Project + ' / ' + spcontact
 
 
This is the statement I want to work but it does not.
 
 
select Specno + '-' + spcompany + ' / ' + Project + ' / ' + spcontact as Order1, Count(*) as OrderCount, 
SUM(QtyShp) AS TotQty, Level=
CASE
	WHEN LEFT(invno,1) = '1' OR LEFT(invno,1) = '2' THEN
	   CASE  	
	   	WHEN SUM(QtyShp) < 250 THEN 'A'
           	WHEN SUM(QtyShp) < 500 THEN 'B'
	   	WHEN SUM(QtyShp) < 1000 THEN 'C'
	   	WHEN SUM(QtyShp) < 5000 THEN 'D'
	   	WHEN SUM(QtyShp) < 10000 THEN 'E'
	   	ELSE 'F'
	   END 
        ELSE
	   CASE  	
		WHEN SUM(QtyShp) < 25 THEN 'A'
		WHEN SUM(QtyShp) < 50 THEN 'B'
		WHEN SUM(QtyShp) < 100 THEN 'C'
		WHEN SUM(QtyShp) < 500 THEN 'D'
		WHEN SUM(QtyShp) < 1000 THEN 'E'
		ELSE 'F'
	   END 
     END 
From SBTInvoice 
where salesmn = 'aparker' 
Group by Specno + '-' + spcompany + ' / ' + Project + ' / ' + spcontact

Open in new window

Comment
Watch Question

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:
Split the second query into two Select statements with a where clause to distinguish between the two sets of data, and use a JOIN to concatenate the two results.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
CASE
      WHEN (LEFT(invno,1) IN ('1','2') AND SUM(QtyShp) < 250)
          OR (LEFT(invno,1) > '2' AND SUM(QtyShp) < 25) THEN 'A'
            WHEN (LEFT(invno,1) IN ('1','2') AND SUM(QtyShp) < 500)
          OR (LEFT(invno,1) > '2' AND SUM(QtyShp) < 50) THEN 'B'
      WHEN (LEFT(invno,1) IN ('1','2') AND SUM(QtyShp) < 1000)
          OR (LEFT(invno,1) > '2' AND SUM(QtyShp) < 100) THEN 'C'
      WHEN (LEFT(invno,1) IN ('1','2') AND SUM(QtyShp) < 5000)
          OR (LEFT(invno,1) > '2' AND SUM(QtyShp) < 500) THEN 'D'
      WHEN (LEFT(invno,1) IN ('1','2') AND SUM(QtyShp) < 10000)
          OR (LEFT(invno,1) > '2' AND SUM(QtyShp) < 1000) THEN 'E'
      ELSE 'F'
END

Author

Commented:
I changed invno in the group by to left(invno,1) and it worked perfectly.  
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.