Sql，获得一个组的平均值，同时逃避where条件 [英] Sql, get the average on a group while escaping the where condition

问题描述

我正在做这个请求：

pre> 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 =[e-mail]
AND tyd.step =0
GROUP BY catalog.id_product，catalog.id_marchand


当然，AVG& COUNT函数不起作用，因为我的条件只得到一行。

我想要做的事情仍然是获取我的独特行，但获得AVG和COUNT函数加工。我可以做两个查询，但我更愿意做一个。

我希望你能帮助我。

谢谢。

PS：我在回答3时提出了其他问题。我正在生气！ / p>

解决方案

尝试使用

  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 =[e-mail]
  

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, 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 = "[e-mail]"
   AND tyd.step = "0"
 GROUP BY catalog.id_product, catalog.id_marchand

But of course the AVG & COUNT function doesn't work because my condition where get only one lines.

What i'd like to do is still getting my unique line but get the AVG and COUNT function working. I can do it with two queries but i'd prefer do one.

I hope you'll be able to help me.

Thanks.

P.S : I put an other problem in answer 3. I'm getting mad !

解决方案

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" 
GROUP BY catalog.id_product, catalog.id_marchand
HAVING tyd.login = "[e-mail]"

这篇关于Sql，获得一个组的平均值，同时逃避where条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！