Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Progress Query - Non group by expression in select clause (7641_

Posted on 2013-11-18
1
Medium Priority
?
1,245 Views
Last Modified: 2013-11-18
Here is a simple query (see attached). When I add the SUM(Case when) clause it fails and get

"Non-group by expression in select clause"  I have tried several things but still can not get to work.
query.docx
0
Comment
Question by:kelsanit
1 Comment
 
LVL 13

Accepted Solution

by:
AielloJ earned 2000 total points
ID: 39657135
kelsanit,

When you use a SUM (or any other aggregate operator) all of the other items in the SELECT clause must use aggregate operators as well.  In your example you must add all the non-aggregate lines to a GOUP BY or similar statement.

SELECT
 "INVOICE_TRL"."Invoice-Num", "INVOICE_TRL"."Invoice-Date", "INVOICE_TRL"."Prod-Pkg-Code", "INVOICE_TRL"."Quantity-Shipped", "INVOICE_TRL"."Cancelled", "INVOICE_TRL"."Type", "INVOICE_TRL"."Merchandise", "INVOICE_TRL"."Packaging-Code", "INVOICE_TRL"."Cust-ID", "INVOICE_TRL"."Sale-Measure", "CUST_SHIPTO"."territory", "INVOICE_TRL"."Product-Code",
Sum(Case When ("INVOICE_TRL"."Invoice-Date">={d '2012-09-30'} AND "INVOICE_TRL"."Invoice-Date"<={d '2012-12-29'}) then  "INVOICE_TRL"."Merchandise" else 0 end) as lastqtrsales
FROM   ("CHEMPAX"."PUB"."CUST-SHIPTO" "CUST_SHIPTO" INNER JOIN "CHEMPAX"."PUB"."INVOICE-HDR" "INVOICE_HDR" ON ("CUST_SHIPTO"."Cust-ID"="INVOICE_HDR"."Cust-ID") AND ("CUST_SHIPTO"."Seq-Num"="INVOICE_HDR"."Shipto-Seq-Num")) INNER JOIN "CHEMPAX"."PUB"."INVOICE-TRL" "INVOICE_TRL" ON ("INVOICE_HDR"."Invoice-Num"="INVOICE_TRL"."Invoice-Num") AND ("INVOICE_HDR"."System-ID"="INVOICE_TRL"."System-ID")
WHERE  "INVOICE_TRL"."Invoice-Date">={d '2012-04-01'}
GROUP BY  "INVOICE_TRL"."Invoice-Num", "INVOICE_TRL"."Invoice-Date", "INVOICE_TRL"."Prod-Pkg-Code", "INVOICE_TRL"."Quantity-Shipped", "INVOICE_TRL"."Cancelled", "INVOICE_TRL"."Type", "INVOICE_TRL"."Merchandise", "INVOICE_TRL"."Packaging-Code", "INVOICE_TRL"."Cust-ID", "INVOICE_TRL"."Sale-Measure", "CUST_SHIPTO"."territory", "INVOICE_TRL"."Product-Code",

Regards,

AielloJ
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

971 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