EDIT: ORA-00936: missing expression EDIT OF [ORA-00933: SQL command not properly ended]

Question

[EDIT] I was told that the query has a Sub Query in it, So here is what i've come up with and what to work on now.[NEW CODE]

 SELECT P.prodid, P.prodname, SUM(O.qtysold) AS "TOTAL SALES"
 FROM ORDERLINE O
 INNER JOIN PRODUCT P
 ON O.prodid = P.prodid
 INNER JOIN ALLOCATION A
 ON P.prodid = A.prodid
 GROUP BY P.prodid, P.prodname
 HAVING (SELECT AVG(score) AS score
            FROM QUALITYCHECK
            HAVING score >= 2)
 ORDER BY 3 ASC;

[ORIGINAL POST] So I've been trying to do different ways of this code to give me the right results. I think this is my most accurate one if it works:

SELECT P.prodid, P.prodname, SUM(O.qtysold) AS "TOTAL SALES"
FROM ORDERLINE O
INNER JOIN PRODUCT P
ON O.prodid = P.prodid
INNER JOIN ALLOCATION A
ON P.prodid = A.prodid
GROUP BY P.prodid, P.prodname
WHERE (SELECT AVG(score) FROM QUALITYCHECK) >= 2
ORDER BY 3 ASC;

~Error is line 8~

So let me explain what I'm trying to accomplish. I'm trying to grab the quantity of sold products from the Orderline table but only where the quality check score average of the product is greater than 2. I would like to try and make this work, but if there is another way of doing it, all suggestions are welcome.

I have another way of doing it also, but it's giving me results of 3 and 15 for the SUM instead of 1 and 5, I supose I will also put that here incase anyone can fix that.

 SELECT P.prodid, P.prodname, SUM(O.qtysold) AS "TOTAL SALES"
 FROM ORDERLINE O
 INNER JOIN PRODUCT P
 ON O.prodid = P.prodid
 INNER JOIN ALLOCATION A
 ON P.prodid = A.prodid
 INNER JOIN QUALITYCHECK Q
 ON A.prodid = Q.prodid
 GROUP BY P.prodid, P.prodname
 HAVING AVG(Q.score) >= 2
 ORDER BY 3 ASC;

Thanks in Advance :D

The `WHERE` needs to go *before* the `group by` – Sep 28 '14 at 09:52 — , Sep 28 '14 at 09:52

abc123 · Answer 1 · 2014-09-28T10:28:11.183

0

 Try this 

SELECT P.prodid, P.prodname, SUM(O.qtysold) AS "TOTAL SALES"
 FROM ORDERLINE O
      INNER JOIN PRODUCT P
        ON O.prodid = P.prodid
      INNER JOIN ALLOCATION A
        ON P.prodid = A.prodid
      INNER JOIN QUALITYCHECK Q
        ON A.prodid = Q.prodid AND AVG(Q.score) >= 2 
      GROUP BY P.prodid, P.prodname
 ORDER BY 3 ASC;

edited Sep 28 '14 at 10:28

answered Sep 28 '14 at 09:45

abc123

262
4
27

This did get rid of the error, but it didn't not give me any results. 0 rows selected. – Jacko Sep 28 '14 at 09:54
your new statement gives me this error ORA-00934: group function is not allowed here – Jacko Sep 28 '14 at 10:33

Bohemian · Answer 2 · 2014-09-28T15:16:06.983

0

Your second query seems worth pursuing. You're getting the wrong sum because the order rows are being joined too many times.

Try grouping on product and quality in isolation:

SELECT P.prodid, P.prodname, SUM(O.qtysold) AS "TOTAL SALES"
FROM ORDERLINE O
INNER JOIN (
    select P.prodid, P.prodname, p.prodid
    FROM PRODUCT P
    INNER JOIN QUALITYCHECK Q ON p.prodid = Q.prodid
    GROUP BY P.prodid, P.prodname, p.prodid
    HAVING AVG(Q.score) >= 2) P ON O.prodid = P.prodid
INNER JOIN ALLOCATION A ON P.prodid = A.prodid
ORDER BY 3

edited Sep 28 '14 at 15:16

answered Sep 28 '14 at 10:13

Bohemian

412,405
93
575
722

Hey man, I see what you're trying to do but it's cause a few erros left right and centre. I suppose understanding the table I have would clarify it – Jacko Sep 28 '14 at 10:33

EDIT: ORA-00936: missing expression EDIT OF [ORA-00933: SQL command not properly ended]

2 Answers2