Sql, get the average on a group while escaping the where condition

Question

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 = "user1@tyd.fr"
   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 !

score 0 · Accepted Answer · answered Jul 31 '12 at 12:50

0

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 = "user1@tyd.fr"

answered Jul 31 '12 at 12:50

Marco

56,740
14
129
152

Thank you very much ! I'm looking for it since two days ! – pierreaurelemartin Jul 31 '12 at 12:57
As you're new here, I remember you should [accept an answer](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) if you think it solved your problem. – Marco Jul 31 '12 at 12:59
Sorry it was a wrong manipulation...! Can you check my answer 3 i still get a problem ? – pierreaurelemartin Jul 31 '12 at 15:49

score 0 · Answer 2 · answered Jul 31 '12 at 13:06

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" 
 GROUP BY catalog.id_marchand, catalog.id_product, catalog.price AS c_price, 
       catalog.img_src, tyd.login

If the precedent answer was working this one is not. I get only the AVG of the selected line. — pierreaurelemartin, Jul 31 '12 at 13:16

pierreaurelemartin · Answer 3 · 2012-07-31T16:38:26.023

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
    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 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.

In fact it only take the first line, no matters the value of ID or something else... — pierreaurelemartin, Jul 31 '12 at 15:04
You're new here, so I give you some advice. Do not change your question if this was already answered and solved. If you need to "increase" your problem, post another question and give reference to previous answer :) — Marco, Jul 31 '12 at 16:53
That's what i did here : http://stackoverflow.com/questions/11744994/sql-having-get-only-the-first-recorded-row-in-table-and-i-want-all thanks for ur advice ! — pierreaurelemartin, Jul 31 '12 at 17:09

Sql, get the average on a group while escaping the where condition

3 Answers3

Linked