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 perfectlyselect 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 + ' / ' + spcontactThis 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

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

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

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.

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

I changed invno in the group by to left(invno,1) and it worked perfectly.

0

Featured Post

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backupâ€¦