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

Become a Premium Member and unlock a new, free course in leading technologies each month.

Solved

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

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
```

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

```
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
```

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

Question has a verified solution.

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

Course of the Month1 day, 18 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.