Geek Answers Handbook

Sql, get the average value for the group, avoiding the conditions where

Sorry my English, but no one can answer in French; -)

I am making this request:

SELECT AVG(tyd.price) AS avg_price, COUNT(tyd.id_product) AS cnt_id_p, catalog.id_marchand, catalog.id_product, catalog.price AS c_price, catalog.img_src, tyd.login AS tyd_l FROM catalog INNER JOIN tyd ON catalog.id_marchand = tyd.id_marchand AND catalog.id_product = tyd.id_product WHERE tyd.login = " user1@tyd.fr " AND tyd.step = "0" GROUP BY catalog.id_product, catalog.id_marchand

But of course, the AVG and COUNT function does not work, because my condition is where there is only one line.

What I would like to do is still getting my unique string, but the AVG and COUNT function works. I can do this with two queries, but I would rather do it.

I hope you can help me.

Thanks.

PS: I put another problem in answer 3. I'm angry!

+4
source share
3 answers

Try

 SELECT AVG(tyd.price) AS avg_price, COUNT(tyd.id_product) AS cnt_id_p, catalog.id_marchand, catalog.id_product, catalog.price AS c_price, catalog.img_src, tyd.login AS tyd_l FROM catalog INNER JOIN tyd ON catalog.id_marchand = tyd.id_marchand AND catalog.id_product = tyd.id_product AND tyd.step = "0" GROUP BY catalog.id_product, catalog.id_marchand HAVING tyd.login = " user1@tyd.fr "
0
source

You must include all columns in the GROUP BY clause that are not part of the aggregate function.

 SELECT AVG(tyd.price) AS avg_price, COUNT(tyd.id_product) AS cnt_id_p, catalog.id_marchand, catalog.id_product, catalog.price AS c_price, catalog.img_src, tyd.login AS tyd_l FROM catalog INNER JOIN tyd ON catalog.id_marchand = tyd.id_marchand AND catalog.id_product = tyd.id_product WHERE tyd.login = " user1@tyd.fr " AND tyd.step = "0" GROUP BY catalog.id_marchand, catalog.id_product, catalog.price AS c_price, catalog.img_src, tyd.login
0
source

I'm sorry, but I'm using the same query with a different table entry, for example:

  SELECT AVG(tyd.price) AS avg_price, COUNT(tyd.id_product) AS cnt, tyd.id_marchand, tyd.id_product, catalog.price AS c_price, tyd.price AS t_price, tyd.amount AS t_am, pro_tyd.amount AS p_am, pro_tyd.price AS p_price, catalog.img_src, tyd.step, tyd.login AS tyd_l FROM catalog INNER JOIN tyd ON catalog.id_marchand = tyd.id_marchand AND catalog.id_product = tyd.id_product AND tyd.step = "1" INNER JOIN pro_tyd ON tyd.id_marchand = pro_tyd.id_marchand AND tyd.id_product = pro_tyd.id_product GROUP BY catalog.id_product, catalog.id_marchand HAVING tyd.login = " user1@tyd.fr "

and it only works when tyd.login = " user3@tyd.fr ", which is the bottom id. not working with user1 or user2 ... I just can't figure out why ...!

Here is the table:

id / id_marchand / id_product / login / price / amount / delay / step / time

29/1/1 / user3@tyd.fr / 344/1/0/1/1343297500

120/1/1 / user2@tyd.fr / 54/1/0/1/1343297504

109/1/1 / user10@tyd.fr / 34/1/0/1/1343298598

When HAVING tyd.login = " user3@tyd.fr " works fine. When user1 or user2 I got 0 lines.

0
source

More articles:


All Articles