CODEWITHSUNDEEP
mysqlsql
yuso88
yuso88

Reputation: 17

How to do 2 queries in the same sql statement?

I have a DB in which I save all the daily visits to a lot of websites, the fields of the day table are name, nvisit and date

I want to make a comparison between the visits of the day before yesterday and those of yesterday, so the result to show should be 3 rows. name, the day before yesterday and yesterday

I have tried with this query but it shows me everything in 2 rows.

SELECT *
FROM (
    SELECT name as nombre, nvisit as anteayer
    from day
    WHERE date < CURDATE() -1 and date > CURDATE() -2
    UNION ALL
    SELECT name as nombre, nvisit as ayer
    from day
    where date < CURDATE() and date > CURDATE() -1
    GROUP by name
    )
    day

What can I do to solve this problem?

When executing the sentence the result is:

|name         |beforeyesterday|
|.............|...............|
|example1.com |2154           |
|example1.com |3215           |
|example2.com |1524           |
|example2.com |2546           |

What I need:

|name         |beforeyesterday|yesterday|
|.............|...............|.........|
|example1.com |2154           |3215     |
|example2.com |1524           |2546     |

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I would want a separate row for each name with the columns for the counts on the various days:

select name,
       sum(case when date < curdate() and date > curdate - interval 1 day
                then nvisit
           end) as yesterday,
       sum(case when date < curdate() - interval 1 day and date > curdate - interval 2 day
                then nvisit
           end) as day_before
from day
group by name;

Upvotes: 1

Related Questions