Menu
I have a products table with product names in different languages:
product-id | lang-no | name
I want to list each product once, but using a different language name.
I dont't have all languages for all product names, so I have to fall back onto another language sometimes.
To pick the language with the lowest or highest number I use
SELECT * FROM products JOIN
(SELECT product-id, MIN(lang-no) AS minlang FROM products GROUP BY product-id)
AS u ON products.product-id = u.product-id AND products.lang-no=minlang
But now I need to define another aggregate function instead of MIN or MAX so I can prefer lang-no 3 for example.
How do i define my own aggregate function in Mysql, eg. some IF logic ?
495
The GROUP BY clause is often used with an aggregate function to perform calculations and return a single value for each subgroup.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Explanation: We apply the aggregate function to a group of sets of tuples using the group by clause. The group by clause must always be used whenever we are willing to apply the aggregate function to a group of sets of tuples.
The GROUP BY clause is normally used along with five built-in, or "aggregate" functions. These functions perform special operations on an entire table or on a set, or group, of rows rather than on each row and then return one row of values for each group.
You can use case with aggregates to specify which value to return if present and if not min (or max) value:
select p.*
from products p
join (
select product_id,
case
when sum(lang_no = 3) > 0
then 3
else min(lang_no)
end as min_lang_no
from products
group by product_id
) p2 on p.product_id = p2.product_id
and p.lang_no = p2.min_lang_no
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
