Sql only gets the first recorded row in the table, and I want everything

Question

Someone already helped me with this request, but I did the adaptation and I had a problem:

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

and it only works when tyd.login = " [e-mail] " , which is the bottom identifier. not working with user1 or user2 ... I just can't figure out why ...! It also works if I group tyd.login, but in this case the AVG and COUNT function only works on one line ...

Here is the table:

 id id_marchand id_product login price amount delay step time 29 1 1 [e-mail] 344 1 0 1 1343297500 120 1 1 [e-mail] 54 1 0 1 1343297504 109 1 1 [e-mail] 34 1 0 1 1343298598 

When HAVING tyd.login = " [e-mail] " works fine. When user1 or user2 I got 0 lines.

thanks for the help

First question: Sql, get the average value for a group, avoiding the where clause

Answer 1

The problem is that your request is not deterministic. You select more columns that you are grouping, where additional columns are not defined by the columns you are grouping. If the latter were true, then it would fall into the ANSII SQL standards, since this is not, in my opinion, the failure of MySQL, that it allows you to execute the statement in general. Other DBMSs went the other way around because they cannot determine whether certain columns are functions of other columns that are not in the group, now they will allow any operator with columns in the selection list that are not contained within the group.

To try to simplify the task, take the following dataset (Table T)

 ID Col1 Col2 1 1 1 2 1 3 

Launch:

 SELECT Col1, MAX(Col2) AS MaxCol2, MIN(Col2) AS MinCol2, AVG(Col2) AS AvgCol2 FROM T GROUP BY Col1 

Will always return

 Col1 MaxCol2 MinCol2 AvgCol2 1 3 1 2 

However, if you throw the ID into the mix

 SELECT ID, Col1, MAX(Col2) AS MaxCol2, MIN(Col2) AS MinCol2, AVG(Col2) AS AvgCol2 FROM T GROUP BY Col1 

There is no way to determine which identifier will be returned, 1 or 2, the most likely result will be

 ID Col1 MaxCol2 MinCol2 AvgCol2 1 1 3 1 2 

However, there is nothing specific in SQL to say that the result cannot be:

 ID Col1 MaxCol2 MinCol2 AvgCol2 2 1 3 1 2 

So, if, if there was a result above, and you added HAVING ID = 1 to the query, you would not get the results because the HAVING clause applies to the data. If you added the identifier to GROUP BY , you would get 2 lines that, as I understand it, are not what you want, and if you added it to WHERE , then your MIN, MAX and AVG functions are affected. Therefore you need to use a subquery. Therefore, in this example, I would use

 SELECT T.ID, T.Col1, MaxCol2, MinCol2, AvgCol2 FROM T INNER JOIN ( SELECT Col1, MAX(Col2) AS MaxCol2, MIN(Col2) AS MinCol2, AVG(Col2) AS AvgCol2 FROM T GROUP BY Col1 ) T2 ON T.Col1 = T2.Col1 WHERE ID = 1 -- OR ID = 2 DEPENDING ON REQUIREMENTS 

To apply this to your situation, the database engine determined that the row to be returned for columns outside the group is a row containing ID = 29. Thus, your HAVING clause applies only to this row, a row with [e-mail] and [e-mail] have already been removed from the results by the time you applied your HAVING clause. You need to perform aggregate functions separately for filtering.

Now I haven’t completely looked back at your schema, but I hope that I will explain the problem of the non-deterministic statement well enough so that you can make any changes to my attempt to rewrite your request

 SELECT Avg_Price, 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 INNER JOIN Pro_tyd ON tyd.id_marchand = pro_tyd.id_marchand AND tyd.id_product = pro_tyd.id_product INNER JOIN ( SELECT ID_Marchand, ID_Product, Step, AVG(tyd.price) AS avg_price, COUNT(tyd.id_product) AS cnt FROM Tyd WHERE Step = '1' GROUP BY ID_Marchand, ID_Product, Step ) Agg ON Agg.id_marchand = pro_tyd.id_marchand AND Agg.id_product = pro_tyd.id_product AND Agg.Step = tyd.Step WHERE tyd.Login = ' [e-mail] ' 

Answer 2

I just want to add this query:

 SELECT * FROM tyd WHERE step = "1" GROUP BY tyd.id_product, tyd.id_marchand HAVING tyd.login = " [e-mail] " 

Has the same problem. Works when tyd.login = "user3" but not when another user asks ...