I have a problem with the SQL query to MySQL: I need to add one field, and for the other to display the last value. Table:

name date interested made_call andrew.h 2011-02-04 10 10 andrew.h 2011-02-11 20 10 andrew.h 2011-02-13 2 10 sasha.g 2011-02-11 5 20 sasha.g 2011-02-12 5 1

This is what I need as a result:

 name date interested made_call andrew.h 2011-02-13 2 30 sasha.g 2011-02-12 5 21

I tried to write such a request

 SELECT a.name,a.date,a.interested,sum(made_call) as made_call FROM `resultboard` a WHERE a.attendence = 1 AND NOT EXISTS (select 1 from resultboard where name = a.name and id > a.id and attendence = 1) GROUP BY name

but as a result, nothing was added, but only the last result was output:

 andrew.h 2011-02-13 2 10 sasha.g 2011-02-12 5 1
  • o_0. I propose to make two requests ... - kirelagin

2 answers 2

 SELECT a.name, t.date, a.interested, t.calls FROM resultboard a JOIN (SELECT name, MAX(date) AS date, SUM(made_call) AS calls FROM resultboard GROUP BY name) AS t ON a.name = t.name AND a.date = t.date

I figured it out myself. Made the "impossible"))

  • Damn .. I thought that you want the last value not for the date, but for that second number :) - cy6erGn0m

What you want to do cannot be done. You are trying to mix search and aggregation (s) in one query. You will not succeed. Make two requests, as suggested by kirelagin. Well, or do some clever JOIN of two subqueries ...

  • By the way, I never understood the reasons for such SQL inventions, personally it’s easier for me to pull out the data with one simple query and to perform all the necessary actions in the resulting array :) - winrarhero
  • @winrarhero Noooo, so here is another! The fact is that the database is optimized for such operations, respectively, will work faster than with the means of the language (especially such as awkward as php). This time. Secondly. Even on the example of this task - it is required to display two numbers for each name. And you want to transfer the entire table from the base to the script. - kirelagin
  • one
    @winrarhero, well, the query in the database you put, if it will, say, 1 million lines, and customers will request the page constantly. Never do that. Better the query will be executed a little longer with small amounts of data, but it will not blunt for large :) - Alex Silaev
  • Thanks for the advice, apparently it will be useful for me to match the sql language part as follows) - winrarhero