Sql, Get The Average On A Group While Escaping The Where Condition

excuse my english but nobody can answer in French ;-) I'm doing this request : SELECT AVG(tyd.price) AS avg_price, COUNT(tyd.id_product) AS cnt_id_p, catalog.id_marchand,

Solution 1:

Try with

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"GROUPBY catalog.id_product, catalog.id_marchand
HAVING tyd.login = "user1@tyd.fr"

Solution 2:

You should include all the columns in the GROUP BY clause that are not part of 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"GROUPBY catalog.id_marchand, catalog.id_product, catalog.price AS c_price, 
       catalog.img_src, tyd.login 

Solution 3:

I'm sorry to ask back but i use the same request with join an other table, like that :

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
    GROUPBY 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 lower id. doesn't work with user1 or user2...I just can't figure 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" it works perfectly. When user1 or user2 i got 0 lines.