CODEWITHSUNDEEP
mysqlsqlcountsumleft-join
Rancbar
Rancbar

Reputation: 184

Sum of Counted records that calculated using "group by" with condition and "group by"

I'm sorry for fuzzy title of this question.

I have 2 Tables in my database and want to count records of first_table using "group by" on a foreign key id that exists in a column of second_table (which stores ids like array "1,2,3,4,5").

id | name | fk_id

 1    |   john    |     1
 2    |   mike    |     1
 3    |   jane    |     2
 4    |   tailor  |     1
 5    |   jane    |     3
 6    |   tailor  |     5
 7    |   jane    |     4
 8    |   tailor  |     5
 9    |   jane    |     5
 10   |   tailor  |     5

id | name | fk_ids | s_fk_id

 1    |    xxx    |  1,5,6     |     1
 2    |    yyy    |  2,3       |     1
 3    |    zzz    |  9         |     1
 4    |    www    |  7,8       |     1

Now i wrote the following query but it not working properly and displays wrong numbers.

I WANT TO:

1-Count records in first_table group by "fk_id"

2-Sum the counted records which exists in "fk_ids"

3-Display the sum result (sum of related counts) grouped by id.

symbol ' ' means ``.

select sum(if(FIND_IN_SET('fk_id', 'fk_ids')>0,'count',0) 'sum', 'count', 'from'.'fk_id', 'second_table'.* FROM 'second_table'
LEFT JOIN
(
    SELECT 'fk_id', count(*) 'count'
    FROM 'first_table'
    group BY 'fk_id'
 ) AS 'from'
ON FIND_IN_SET('fk_id', 'fk_ids')>0
WHERE  'second_table'.'s_fk_id'=1
GROUP BY 'id'
ORDER by 'count' DESC

This table has many data and we have no plan to change the structure.

Edit:

Desired output:

id | name | sum

 1    |    xxx    | 7  (3+4+0)
 2    |    yyy    | 2  (1+1)  
 3    |    zzz    | 0  (0)    
 4    |    www    | 0  (0+0)

Upvotes: 0

Views: 64

Answers (1)

Rancbar
Rancbar

Reputation: 184

After two holidays i came back to work and found out that the "FIND_IN_SET" function is not working properly with space contained string.

And the problem is that i was ignored the spaces too, (same as this question)

Finnaly this query worked:

select sum(`count`) `sum`, `count`, `from`.`fk_id`, `second_table`.* FROM `second_table`
LEFT JOIN
(
    SELECT `fk_id`, count(*) `count`
    FROM `first_table`
    group BY `fk_id`
 ) AS `from`
ON FIND_IN_SET(`fk_id`, replace(`fk_ids`,' ',''))>0
WHERE  `second_table`.`s_fk_id`=1
GROUP BY `id`
ORDER by `count` DESC

And the magic is replace(fk_ids,' ','')

Upvotes: 0

Related Questions