Mysql query output right, count wrong

Question

SELECT 
    count(A.gross_c) AS cnt, 
    SUM(A.gross_c) AS Gross_c, 
    B.store_name
FROM hr_location_c_verified A 
JOIN hr_stores B ON A.c_location = B.id
WHERE A.c_ref_fkid IS NULL
GROUP BY A.c_location

I am executing this query, the output result coming correct, but count showing wrong.

Please help me.

Please show some sample data and what's wrong count is comming? — Himanshu
– Himanshu, Commented Mar 7, 2013 at 12:15
I am trying to count the number of rows, Gross_c and store_name coming correct but cnt output wrong — Banala Ramu
– Banala Ramu, Commented Mar 7, 2013 at 12:16
group by expression should always contain non aggregated colums of the select (here store_name, and add c_location on the select), else strange things may happen. other databases would reject your query as invalid. — regilero
– regilero, Commented Mar 7, 2013 at 12:56

brain · Accepted Answer · 2013-03-07 12:17:29Z

2

It's likely because of your JOIN, producing more rows.

One way is to use the DISTINCT keyword.

SELECT 
    count(DISTINCT A.gross_c) AS cnt, 
    SUM(A.gross_c) AS Gross_c, 
    B.store_name FROM hr_location_c_verified A 
JOIN hr_stores B ON A.c_location = B.id
WHERE A.c_ref_fkid IS NULL
GROUP BY A.c_location

answered Mar 7, 2013 at 12:17

brain

2,5271 gold badge14 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query output right, count wrong

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related