Solved

Nested CASE statement with aggregate function

Posted on 2009-07-07
4
549 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
4 Comments
 
LVL 4

Accepted Solution

by:
Woodz earned 500 total points
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
I changed invno in the group by to left(invno,1) and it worked perfectly.  
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

763 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

6 Experts available now in Live!

Get 1:1 Help Now