CODEWITHSUNDEEP
phpmysql
EnexoOnoma
EnexoOnoma

Reputation: 8836

How to edit my query in order to calculate the difference between two numbers?

I am using the following query to find the pageviews of my pages in the last 2 days

SELECT PAGE , COUNT( * ) AS poso, TIMESTAMP
FROM  `behaviour` 
WHERE DATE( TIMESTAMP ) >= DATE_SUB( CURDATE( ) , INTERVAL 1 
DAY ) 
GROUP BY PAGE , DATE( TIMESTAMP )

for example

+-----------------+--------+------------------------+
| PAGE            | poso   | TIMESTAMP              |
+-----------------+--------+------------------------+
| a page          | 6      | 2017-01-07 01:12:53    |
| a page          | 9      | 2017-01-08 01:06:19    |
+-----------------+--------+------------------------+

I am not interested in showing the results, but what I want to have (let's say in two columns) is all the pages and to calculate the pageview difference between each page. In the above example the difference for a page is 3.

How can I have this in a different column?

UPDATE

To answer the comment of Santi, I would like to use the most recent date minus the previous date to have the calculation. Also, the most recent date is the one that I want to include in the row.

At the end of the day, I will collect the 2 or 3 pages with the most positive difference.

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can do this using conditional aggregation:

SELECT PAGE, COUNT( * ) AS poso,
       (SUM(DATE(TIMESTAMP) = CURDATE()) - 
        SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 
DAY))
       ) diff
FROM  `behaviour` 
WHERE DATE( TIMESTAMP ) >= DATE_SUB( CURDATE( ) , INTERVAL 1 
DAY ) 
GROUP BY PAGE;

Upvotes: 1

Related Questions