INNER JOIN causing 'Non-group-by expression in select clause'

Question

A question from a noob SQL user, using MS SQL Server Report Builder 2016.

I'm attempting to construct a query on three tables: PV_Job (header for a job), PV_JobLine (job lines attached to job header) and PV_SOrderLine (sales order lines).

What I'm specifically attempting is, for every PV_Job, find any PV_JobLines attached and then return the SUM of the PV_JobLine's OrderedQty field. (There are other fields and left joins which I've omitted from the below query as I just wanted to isolate the error as much as possible.)

I'm doing this via a left join onto PV_JobLine from PV_Job.

PV_Job also needs to inner join to PV_SOrderLine, as there needs to exist a Sales Order Line for every Job Line.

I'm just getting an error when I attempt to run the query:

Non-group-by expression in select clause

Here's the query I'm attempting to run below:

SELECT 
    "PV_Job1"."JobCode", 
    "PV_Job1"."JobName", 
    "PV_Job1"."CompNum", 
    "PV_Job1"."CompletedDate", 
    "PV_Job1"."JobCreateDate", 
    "PV_Customer1"."CustName", 
    "PV_Job1"."ActTotalCost",
    "JobLine"."SumOrdQty" AS "OrdQty"

FROM  "VISION"."PUB"."PV_Job" "PV_Job1"

        LEFT JOIN "VISION"."PUB"."PV_Customer" "PV_Customer1" 
             WITH (INDEX(CustCode))
               ON ("PV_Job1"."CompNum"="PV_Customer1"."CompNum")
              AND ("PV_Job1"."CustCode"="PV_Customer1"."CustCode")

        LEFT JOIN (SELECT 
                       "PV_JobLine1"."CompNum",
                       "PV_JobLine1"."JobCode",
                       "PV_JobLine1"."PlantCode",
                       "PV_JobLine1"."JobLineNum",
                       SUM("PV_JobLine1"."OrderedQty") AS "SumOrdQty"

                   FROM "VISION"."PUB"."PV_JobLine" "PV_JobLine1"

                   INNER JOIN "VISION"."PUB"."PV_SOrderLine" "PV_SOrderLine1" WITH (INDEX(OrderLine))
                           ON ("PV_JobLine1"."CompNum"="PV_SOrderLine1"."CompNum")
                          AND ("PV_JobLine1"."PlantCode"="PV_SOrderLine1"."PlantCode")
                          AND ("PV_JobLine1"."SOrderNum"="PV_SOrderLine1"."SOrderNum")
                          AND ("PV_JobLine1"."SOrderLineNum"="PV_SOrderLine1"."SOrderLineNum")

                    WITH (INDEX(JobLineIdx))

                    GROUP BY "PV_JobLine1"."CompNum",
                             "PV_JobLine1"."JobCode",
                             "PV_JobLine1"."PlantCode",
                             "PV_JobLine1"."JobLineNum"

        ) "JobLine"
         ON ("PV_Job1"."CompNum"="JobLine"."CompNum")
        AND ("PV_Job1"."JobCode"="JobLine"."JobCode")
        AND ("PV_Job1"."PlantCode"="JobLine"."PlantCode")
        AND ("JobLine"."JobLineNum" IN (1,2,3,4,5))



 WHERE  ("PV_Job1"."CompNum"=7 
    AND "PV_Job1"."CompletedDate" IS  NULL  
     OR "PV_Job1"."CompletedDate">{d '2018-02-21'} 
    AND "PV_Job1"."JobCreateDate"<={d '2018-02-21'} 
    AND "PV_Job1"."ActTotalCost"<>0)

 GROUP BY
    "PV_Job1"."JobCode", 
    "PV_Job1"."JobName", 
    "PV_Job1"."CompNum", 
    "PV_Job1"."CompletedDate", 
    "PV_Job1"."JobCreateDate", 
    "PV_Customer1"."CustName", 
    "PV_Job1"."ActTotalCost",
    "OrdQty"

If I remove the whole INNER JOIN block, the query will run; with it added, it fails with the above error. I can't quite understand exactly what I need to do further to get it correct, or if maybe perhaps there's a better method.

(I'm doing the SUM in the PV_JobLine area because this query originally had more LEFT JOINS for other fields and it was causing an issue with duplication, so attempted to fix using the answer here: Prevent duplicate values in LEFT JOIN)

Apologies if anything is vague or seems misunderstood from my end.. let me know if I can clarify anything further!

Remove stuff until it works, then you'll see where the problem is. — jarlh, Mar 27 '18 at 14:04
Is this really SQL Server? I'm a little confused regarding all the double quotes? — MiguelH, Mar 27 '18 at 14:27
@MiguelH: Double quotes are Standard SQL, supported by SQL Server for years. Those `[]` are proprietary, you lock into MS when you use them :-) — dnoeth, Mar 27 '18 at 14:29
@dnoeth. OK fair enough! Just never seen it like this before! I've always used [ ] without a second thought! — MiguelH, Mar 27 '18 at 14:31
Are you sure that all the `WITH INDEX` are in the right place? `WITH (INDEX(JobLineIdx))`? — dnoeth, Mar 27 '18 at 14:33
Are you intending to use the alias `OrdQty` in the group by rather than the column name - `JobLine"."SumOrdQty` ? — BJones, Mar 27 '18 at 16:02
@dnoeth that appears to be it! I moved `WITH (INDEX(JobLineIdx))` above the INNER JOIN and it ran fine. I think I got stupid and forgot about the `WITH (INDEX(OrderLine))` already attached to the inner join. — klabelkholosh, Mar 27 '18 at 19:55

INNER JOIN causing 'Non-group-by expression in select clause'

0 Answers0