Solved

Nested CASE statement with aggregate function

Posted on 2009-07-07
4
564 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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 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