Percent needed

[milton747]

I'm calulating each order line as a % of the total.
OK, but I'm getting too many rows per order.
EG: There are 3 lines in order #6. I get 9 rows back, 3 for each line. I was hoping for 1 row with the percentage in PCT. (I cannot use DISTINCT as there are lines with same OID and QTY)
I've tried GROUP BY in several places with no luck.

Thanks
Milt.


SELECT
PCTABLE.OID, PCTABLE.QTY,

(SELECT SUM(PCTABLE.QTY)
From PCTABLE
where PCTABLE.OID = T2.OID
) AS CT,

T2.QTY / CT * 100 AS PCT

From PCTABLE, PCTABLE AS T2

where PCTABLE.OID = T2.OID




-- eom --

 

[lameid]

And the problem is...

(I cannot use DISTINCT as there are lines with same OID and QTY)

You can't join do a self join on the table and a field that is not unique and expect to not repeat columns. The good news is that your have unnecessarily done the self join.

You need the sub query to get CT but you do not need PCTABLE more than once in your main query which is what is giving you duplicates.

Fix that, conider what that does to your where clause and
look at the expression for PCT.