mardi 14 juin 2016

How can I add a where exists statement in this "group by" mysql query?

I have a query that returns the range of a value within groups in the table.

Now I want to check for every group if a value "results" occurs in column "pages", and only if the statements holds true, return the row.

A example of the table structure:

   ID  pages    start_time
    1   splash   12
    1   view     14
    1   comments 16
    1   results  18
    2   splash   1
    2   view     13
    2   comments 14
    2   comments 15
    3   test     2
    3   splash   3
    3   results  35
    3   view     26

From this example, the following should be returned:

   ID   time_range
    1    6
    3    33

because ID=2 doesn't have row "results" in column pages.

I have the following query, but don't get the additional condition to work.

SELECT MAX(start_time)-MIN(start_time) 
FROM mytable 
WHERE country="United States"
AND (here should come the if-results-in-pages-exists statements) 
GROUP BY my_id;

How can I add this condition properly?

Aucun commentaire:

Enregistrer un commentaire