[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1344
  • Last Modified:

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

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
kelsanit
Asked:
kelsanit
1 Solution
 
AielloJCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now