asked on

Select Query - Group By Function Producing Unexpected Results

Hi,

I'm trying to iron out a crease in a forecasting tool using MySQL. I've embedded a jpeg to show the result sets that are being produced using a number select query's to highlight a problem that I'm struggling with.

In short the problem is this illustrated in the embedded file below:

In the first select statement the select query pulls back a search of the goodsInTrans table ( a view of incoming stock) to reveal that 2 lines have been ordered on two separate occasions, hence, the 2 separate results, so far so good.

In the second select statement I pull back what the current stock holding is for the item from the inventory table, again no problem.

However, in the third select statement I was hoping for the results to be grouped into 1 row showing:-

qtyInStock = -18,
GIT = 32,
FCAST_Qty = 14.

Instead the results are split against the 2 separate lines from the goodsInTrans table ... struggling to understand where and why it went wrong?

Please help.

J

Pawan Kumar

Hi,
Please change group by to below

GROUP BY inventory.PartDescription , inventory.qtyInstock

Hope it helps!

Ridgejp

ASKER

I tried that before it produces this error: -

Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'goodsInTrans.unitsRequired' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Pawan Kumar

Hi,
Please try full code below-

--

SELECT 
partDescription,
QtyInStock,
SUM(GIT) GIT,
SUM(FCast_Qty) FCast_Qty
FROM 
(
	SELECT 
	partDescription,
	QtyInStock,
	unitsRequired AS GIT,	(If(ISNULL(goodsintrans.unitsRequired),(inventory.qtyInstock)+0,sum(inventory.qtyInstock)+(goodsintrans.unitsRequired))) AS FCast_Qty
	from inventory left join goodsintrans ON partDescription = sku
	WHERE supplierCompany like "Dummy Holding Ltd" and partDescription LIKE "%litre%"
	GROUP BY inventory.PartDescription , inventory.qtyInstock,goodsintrans.unitsRequired
)k GROUP BY PartDescription , qtyInstock

--

Open in new window

Hope it helps!

Ridgejp

ASKER

Posted following: -

partDescription,
    ->                     qtyInStock,
    ->                     SUM(GIT) GIT,
    ->                     SUM(FCast_Qty) FCast_Qty
    ->                     FROM 
    ->                     (
    ->                     SELECT 
    ->                     partDescription,
    ->                     QtyInStock,
    ->                     unitsRequired AS GIT,    
    ->                     (If(ISNULL(goodsintrans.unitsRequired),(inventory.qtyInstock)+0,sum(inventory.qtyInstock)+(goodsintrans.unitsRequired))) AS FCast_Qty
    ->                     from inventory left join goodsInTrans ON partDescription = sku
    ->                     WHERE supplierCompany like "Dummy Holding Ltd" and partDescription LIKE "%litre%"
    ->                     GROUP BY inventory.partDescription , inventory.qtyInstock, goodsInTrans.unitsRequired
    ->                     )GROUP BY partDescription, qtyInstock;

Open in new window

Got the following error: -

ERROR 1248 (42000): Every derived table must have its own alias

J

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VanDongen Consulting

You may need to keep qtyinstock as part of your group by since you are not using an aggregate function on it. The problem really is the last column in your group by clause, because you have 2 different values for the number requirrd, you are getting two rows. remove the unitsrequired from your group by and it should work.

Ridgejp

ASKER

Tweaked the syntax a little but that did it! Thanks

SELECT
p as partDescription,
q as qtyInStock,
SUM(GIT) as GIT,
SUM(FCast_Qty) as FCast_Qty
FROM
(
SELECT
inventory.partDescription as p,
inventory.qtyInStock as q,
unitsRequired AS GIT,
(If(ISNULL(goodsInTrans.unitsRequired),(inventory.qtyInStock)+0,sum(inventory.qtyInStock)+(goodsInTrans.unitsRequired))) AS FCast_Qty
from inventory left join goodsInTrans ON partDescription = sku
WHERE supplierCompany like "Dummy Holding Ltd" and partDescription LIKE "%litre%"
GROUP BY inventory.partDescription , inventory.qtyInStock, goodsInTrans.unitsRequired
)k GROUP BY p , q

Pawan Kumar

Glad to help!