Solved

Nested CASE statement with aggregate function

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

732 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