0

Please help. I'm trying to update this one table with the current count of assets our products have. If the product already exist in the table, it should update the most updated count of the product. However, using the query below, mysql is returning me

"ERROR 1111 (HY000): Invalid use of group function".

I can't determine what's my error or if it is really valid to use count in function 'on duplicate key':

INSERT INTO report_count_assets
    SELECT products.product_id, 
    count(product_assets.asset_id),
    count(case when assets.asset_type_id=1 THEN 1 END), 
    count(case when assets.asset_type_id=2 THEN 1 END), 
    count(case when assets.asset_type_id=3 THEN 1 END), 
    count(case when assets.asset_type_id=11 THEN 1 END) 
    FROM products 
    LEFT JOIN product_assets USING (product_id) 
    LEFT JOIN assets USING (asset_id)
    WHERE products.brand_id=671

ON DUPLICATE KEY UPDATE
    asset_count = count(product_assets.asset_id),
    asset_type_image = count(case when assets.asset_type_id=1 THEN 1 END), 
    asset_type_video = count(case when assets.asset_type_id=2 THEN 1 END), 
    asset_type_sound = count(case when assets.asset_type_id=3 THEN 1 END), 
    asset_type_install = count(case when assets.asset_type_id=11 THEN 1 END);
Improve this question
2
  • this Question may help u Commented Feb 4, 2012 at 7:07
  • Hi @diEcho, I'm not particularly looking for what products have this certain numbers in their assets. My concern is the update part of the query when the product is already present in the table. I don't know how select-join query can be implemented on 'ON DUPLICATE KEY UPDATE' Commented Feb 4, 2012 at 7:11

2 Answers 2

Reset to default
3

I don't think you can use aggregate functions in the ON DUPLICATE. MySQL sees your SQL sort of like this:

insert into report_count_assets
expr
on duplicate key update
...

The ON DUPLICATE doesn't know what's going on in expr, it only knows that it has a single duplicate row to deal with. Without knowing what's going on inside expr, there is no context for the counts to operate on. Also, you're supposed to use values in the UPDATE:

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement.

And values(count(x)) is not valid syntax. But values(column_name) is valid so this should work:

INSERT INTO report_count_assets
(product_id, asset_count, asset_type_image, asset_type_video, asset_type_sound, asset_type_install)
    SELECT products.product_id, 
    count(product_assets.asset_id),
    count(case when assets.asset_type_id=1 THEN 1 END), 
    count(case when assets.asset_type_id=2 THEN 1 END), 
    count(case when assets.asset_type_id=3 THEN 1 END), 
    count(case when assets.asset_type_id=11 THEN 1 END) 
    FROM products 
    LEFT JOIN product_assets USING (product_id) 
    LEFT JOIN assets USING (asset_id)
    WHERE products.brand_id=671
ON DUPLICATE KEY UPDATE
    asset_count = values(asset_count),
    asset_type_image = values(asset_type_image), 
    asset_type_video = values(asset_type_video), 
    asset_type_sound = values(asset_type_sound), 
    asset_type_install = values(asset_type_install);

I had to guess the name of the product_id column in report_count_assets.

If that doesn't work (as apparently it doesn't), then you can do it the hard way by precomputing the SELECT. Create a temporary table:

create temporary table t (
    product_id int,
    product_count int,
    assets1 int,
    assets2 int,
    assets3 int,
    assets11 int
)

Populate it:

INSERT INTO t (product_id, product_count, assets1, assets2, assets3, assets11)
SELECT products.product_id, 
count(product_assets.asset_id),
count(case when assets.asset_type_id=1 THEN 1 END), 
count(case when assets.asset_type_id=2 THEN 1 END), 
count(case when assets.asset_type_id=3 THEN 1 END), 
count(case when assets.asset_type_id=11 THEN 1 END) 
FROM products 
LEFT JOIN product_assets USING (product_id) 
LEFT JOIN assets USING (asset_id)
WHERE products.brand_id=671

And then use that temporary table to do the insert that you really want to do:

insert into report_count_assets
    select product_id, product_count, assets1, assets2, assets3, assets11
    from t
on duplicate key update
    asset_count = values(product_count),
    asset_type_image = values(assets1),
    asset_type_video = values(assets2),
    asset_type_sound = values(assets3),
    asset_type_install = values(assets11)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi @mu is too short, thank you so much! You saved a lot of blood of me.
@Jan-DeanFajardo: I have much nicer solution this morning if you're interested.
@Jan-DeanFajardo: You just use values inside the ON DUPLICATE section as in my update. I don't know what I was thinking with all that temp table stuff, target fixation perhaps. I blame answering right before going to bed :)
well actually what you did about the temp table solved my problem. The values() inside ON DUPLICATE didn't work since it's only getting the current value of fields and not the updated value I'm getting from the SELECT clause.
@Jan-DeanFajardo: I put the temp table approach back, what ever works :)
0

Try to group by product_assets.asset_id:

.
.
WHERE products.brand_id=671
group by product_assets.asset_id
ON DUPLICATE KEY UPDATE
    asset_count = count(product_assets.asset_id)
.
.
Improve this answer

1 Comment

Also see this link

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.