Select same table twice, in single query

Question

My table:

id | views | date
1  | 100   | 2017-03-09
2  | 150   | 2017-03-10
3  | 300   | 2017-03-11
4  | 350   | 2017-03-12

I need to calculate visit count difference between days something like this

2017-03-12-->Visitors:350
2017-03-11-->Visitors:300
Difference between days:50

2017-03-11-->Visitors:300
2017-03-10-->Visitors:150
Difference between days:150

2017-03-10-->Visitors:150
2017-03-09-->Visitors:100
Difference between days:50

and so on...

I managed to get similar results, but not exatcly what i wanted

$sql = "SELECT * FROM `table` ORDER BY `table`.`id` DESC";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result))
{

    $t = $row['views'];  
    $dat = $row['date'];  


    $sql1 = "SELECT * FROM `table` ORDER BY `table`.`id` DESC LIMIT 1, 99";
    $result1 = mysql_query($sql1) or die(mysql_error());
    while($row1 = mysql_fetch_array($result1))
    {

        $y = $row1['views'];  
        $dat1 = $row1['date'];  

        $d = $t-$y;

        echo "{$dat}-->Visitors:{$t}";
        echo "<br/>";
        echo "{$dat1}-->Visitors:{$y}";
        echo "<br/>";
        echo "Difference between days:{$d}";
        echo "<br/><br/><br/>";                     
    }                       
}

So i guess i need to select same table twice with one query.

bobflux · Accepted Answer · 2017-03-31T10:42:27.393

No need for SQL acrobatics here.

You are displaying the lines in date order. Simply keep the count from last line inside a variable, substract in php, and you have your difference.

Remove your nested loops. You only need one loop.

$last_views = null;

while($row = mysql_fetch_array($result))
{
    $views = $row['views'];  
    $dat   = $row1['date'];  

    if( $last_views === null ) 
         $delta_views = "";
    else
         $delta_views = $last_views - $views;

    $last_views = $views;

    echo "{$dat}-->Visitors:{$views}";
    echo "<br/>";
    echo "Difference between days:{$delta_views}";
    echo "<br/>";                     
}

Just use a variable to store the previous row. And handle the edge case where there initially is no previous row however you want. — Tim Biegeleisen, Mar 31 '17 at 10:42

juergen d · Answer 2 · 2017-03-31T11:01:47.267

0

Group and order by the date. Then you can use a variable to store the last view value in it and compare it to the current one.

select date, 
       sum(views) as currentViews
       sum(views) - @previous as viewsDiff, 
       @previous := sum(views)
from your_table
cross join (select @previous:= 0) p
group by date
order by date

edited Mar 31 '17 at 11:01

answered Mar 31 '17 at 10:33

juergen d

201,996
37
293
362

You might want to add a demo for this as most people won't be familiar with this query. – Tim Biegeleisen Mar 31 '17 at 10:38

score 0 · Answer 3 · edited May 23 '17 at 12:17

0

Try this to handle it in MySQL.

http://rextester.com/XXJWR75843

set @views1=0;
select t.* ,views-lag_views from (
select t1.*,@views1 lag_views , @views1:=views curr_views
from Table1 t1
    ) t

But you need to figure out a way to ignore first value, as it gives you same value as views.

Thanks for this answer to replicate lag function.

edited May 23 '17 at 12:17

Community

1
1

answered Mar 31 '17 at 10:45

Utsav

7,914
2
17
38

Select same table twice, in single query

3 Answers3