Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Nested CASE statement with aggregate function

Posted on 2009-07-07
4
Medium Priority
?
588 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

0
Comment
Question by:twestfall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 4

Accepted Solution

by:
Woodz earned 2000 total points
ID: 24795062
The problem is that you want to use the INVNO field as part of the selection expression, which operates on the result of the table query. The table query is not returning the INVNO field, as it is hidden by the group by clause. To resolve this, you want to have the INVNO field available to the selection expression, which means that it needs to be returned by the group by expression. Simply add ", invno" to the end of the group by expression.
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, invno

Open in new window

0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24795107
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.
0
 
LVL 32

Expert Comment

by:awking00
ID: 24795110
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
0
 

Author Closing Comment

by:twestfall
ID: 31600629
I changed invno in the group by to left(invno,1) and it worked perfectly.  
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 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