Insert Count of a join table

Question

I have two tables. One is a category ID, the other one is a product table. I would like to count how many products of each category ID, and the query is below.

SELECT hkgg_emall_goods_class.gc_id, COUNT(*)  as productcount
FROM hkgg_emall_goods_class
LEFT JOIN hkgg_emall_goods
ON hkgg_emall_goods.gc_id=hkgg_emall_goods_class.gc_id GROUP BY  hkgg_emall_goods_class.gc_id ;

It shows what I want, except the query shows some rows to have count of 1 even they have no products associated, and some row as 1 count when they actually have one product associated.

I want your advice on 1) how to solve this problem 2) I have added the gc_productcount column in the category table. How can I insert the count query into the gc_productcount column for every row?

INSERT INTO `hkgg_emall_goods_class.gc_productcount`

This query is not working well when I put it in front of the select count query.

P.S. I have browsed the other thread in stackoverflow, but luck is not good enough to browse a similar solution.

Thank you in advance.

Have you tried GROUP_CONCAT()?? According to http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html; this function returns a string result with the concatenated non-NULL values from a group. — PseudoAj, May 06 '16 at 03:00
I have not tried it yet; do you have any idea on how to insert the count query result into the column for every row ? — Louis Cheng, May 06 '16 at 03:44

score 0 · Answer 1 · answered May 06 '16 at 04:51

0

Assuming hkgg_emall_goods table has a primary or at least a unique key, that's what you want to count. i.e. you don't want to COUNT(*), you want to COUNT(hkgg_emall_goods.id).

So assuming that primary key is hkgg_emall_goods.id then your query will look like this:

SELECT
    hgc.gc_id,
    COUNT(hg.id) AS productcount
FROM hkgg_emall_goods_class hgc
    LEFT JOIN hkgg_emall_goods hg ON hg.gc_id = hgc.gc_id
GROUP BY
    hgc.gc_id

answered May 06 '16 at 04:51

Unix One

1,151
7
14

and how would i be able to insert the count query into the column ? – Louis Cheng May 06 '16 at 04:55
1

@LouisCheng are you trying to insert into the same table that you're joining to get the counts? Wouldn't that be an update instead of an insert? It's unclear to me what you're trying to accomplish with that, and what you expect as the outcome. – Unix One May 06 '16 at 05:08
I want to move the query column to the productcount column as the productcount is the count of products matching the ID.. Yes, i am thinking as an update as well but i did not know what is the table name of a joined query.. – Louis Cheng May 06 '16 at 07:27
That's generally not recommended. But if you still want to do it, for whatever reason, now that you have the `SELECT`, you can `JOIN` against it for your `UPDATE` query, or use a sub-query like here - http://stackoverflow.com/questions/6135226/update-a-column-with-a-count-of-other-fields-is-sql ... Note that this `UPDATE` is going to be a lot more inefficient than the `SELECT` itself. – Unix One May 06 '16 at 14:43

Insert Count of a join table

1 Answers1