MYSQL Count group by rows ignoring effect of JOIN and SUM fields on Joined tables?

Try COUNT(distinct Orders. Order_id) as number_of_orders as in SELECT customer_id, COUNT(distinct Orders. Order_id) as number_of_orders, SUM(Details.

Ordered_qty) AS total_qty_ordered, (select SUM(Parcels. Picked_qty) FROM Parcels WHERE Parcels. Detail_id=Detail.Id ) AS total_qty_picked FROM Orders LEFT JOIN Details ON Details.

Order_id=Order. Id GROUP BY Orders. Customer_id EDIT: added an other select with subselect.

Try COUNT(distinct Orders. Order_id) as number_of_orders, as in SELECT customer_id, COUNT(distinct Orders. Order_id) as number_of_orders, SUM(Details.

Ordered_qty) AS total_qty_ordered, (select SUM(Parcels. Picked_qty) FROM Parcels WHERE Parcels. Detail_id=Detail.Id ) AS total_qty_picked FROM Orders LEFT JOIN Details ON Details.

Order_id=Order. Id GROUP BY Orders. Customer_id EDIT: added an other select with subselect.

Ok, cool I can see why the COUNT should work with distinct, but for some reaon it always gives me '1' as number_of_orders. – user277260 Aug 25 at 15:23 Strange. Can you make sure there is more then one order_id by running again my answer (note that I edited the select and added order_id to the group by clause.

Thx! – bpgergo Aug 25 at 15:28 Ah, thanks. I had the wrong GROUP BY which was causing the error.

Both of your solutions work. The SUM fields seem ok as well now, though I need to check them thoroughly. All very big numbers.

Thanks for your help! – user277260 Aug 25 at 15:33 OK, then I edit back the answer for fellow SO users, who will find this answer later ... – bpgergo Aug 25 at 15:41 Ah. SO still a problem.

If I do the first join then all is well with the numbers. But the second join causes the SUM on the first join field to we wrong. But the SUM on its own field is right.

– user277260 Aug 25 at 15:48.

Simplify by breaking it up in to separate queries, and if you want a single call to get the results, put the queries in a stored procedure, using temp tables.

There is a reason, yes. This query will itself be a View, and the app will run other queries using this view. This view will be emulating a data structure that will be available to the application in the future, but is not available yet.

But the application needs to be developed now. So I need to use a View for now to bridge the gap between the old and new data structures while building the app. – user277260 Aug 25 at 15:04 If nothing else, you can always return the individual count/sum values as results from subqueries, which'd solve the parent grouping problem.

– Marc B Aug 25 at 15:35 Yeah. Ive jsut solved it doing exactly that. Subquery to return the SUM for the 2nd Join.

– user277260 Aug 25 at 16:50.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.