Why this query with LIMIT still sums all the values MYSQL PHP

Databases
,
1

Hello everyone!

I cant figure out why this result i get keeps summing all table values not just for last 10 records??

Here are code:

SELECT SUM(amount) as amount,  SUM(cost) as cost

FROM 
	(
		SELECT amount, cost

		FROM my_table
		WHERE ((status='20' OR status='30') && status!='40')
		GROUP BY product_umo, extra_umo
		ORDER BY date DESC, id DESC LIMIT 10
	) as tot
2

SOLVED!
GROUP BY product_umo, extra_umo just needed to be outside subquery!

SELECT SUM(amount) as amount,  SUM(cost) as cost

FROM 
	(
		SELECT amount, cost

		FROM my_table
		WHERE ((status='20' OR status='30') && status!='40')
		/*GROUP BY product_umo, extra_umo*/
		ORDER BY date DESC, id DESC LIMIT 10
	) as tot
GROUP BY product_umo, extra_umo
1 Like
3

couple comments, if i may?

first, your results SUM(amount) as amount, SUM(cost) as cost

depending on how many combinations of product_umo, extra_umo there are, you will get that many rows, and you will have no way of knowing which sums belong to which

second, please use standard sql operators whenever possible, so change this –

WHERE ((status='20' OR status='30') && status!='40')

to this –

WHERE ((status='20' OR status='30') AND status!='40')

now please note that if status is either 20 or 30, then it cannot be 40!!

also, please don’t ( use (unnecessary) parentheses )

WHERE status='20' OR status='30'
1 Like
4

For now i think it shows all correct!

My mistake :stuck_out_tongue:

Is that big deal or just less code?

Thanks for comments :slight_smile:

5

“for now”

1 Like
6

If you increase the LIMIT so you have more rows to look at, SELECT more fields, and remove the SUMs, do the results provide some insight? eg.

SELECT product_umo 
   , extra_umo 
   , amount 
   , cost 
FROM ( SELECT product_umo 
         , extra_umo 
         , amount 
         , cost 
       FROM my_table 
       WHERE status = '20' OR status = '30' 
       ORDER BY date DESC, id DESC 
       LIMIT 100 ) AS tot 
GROUP BY product_umo, extra_umo

IMHO, for something involving “products” and “cost” (money) it would be wise to more than “think” it’s correct and longer than “for now”.

7

how, after all these years, can you suggest something like that?

please see http://download.nust.na/pub6/mysql/doc/refman/5.1/en/group-by-hidden-columns.html –

8

My intent was not to suggest it was a correct query, but that the results might allow a realization that the GROUP BYs were not doing what it was thought they were doing.

Admittedly, not having the table to work with I did not test the query so it could very well be totally off base from what I had intended it to do.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.