MySQL Multiple queries into single one

Question

I have a mysql table like with the following rows:

id  |  event_type  |  occurance_date  |  ipaddress

I want to get the days, unique visits and total visits / day. This is pretty easy to do with 2 queries but I want to make only one.

So i thought To get the days i can

SELECT DISTINCT(occurance_date) as day FROM statistics WHERE event_type = 'visit' 
ORDER BY id DESC LIMIT 14;

To get unique visits / day

SELECT COUNT(DISTINCT(ipaddress)) as unique_visits FROM statistics WHERE occurance_date = '2011-05-11';

To get all visits / day

SELECT COUNT(id) as total_visits FROM statistics WHERE occurance_date = '2011-05-11';

The problem is how can I make a single query to return me the all the days, with unique_visits and total_visits.

(2011-05-11 should be replaced by date, it's just an example)

Any suggestion is more then welcomed.

Thanks.

Johan · Accepted Answer · 2011-06-05 13:10:20Z

3

select count(*) as total_visits, count(distinct(s1.ipaddress)) as unique_visits 
FROM statistics s1 
WHERE event_type = 'visit'
GROUP BY occurrence
ORDER BY unique_visits;

Annotated version:

First we do a SELECT count(all rows)

select count(*) as total_visits,

Plus a count of the distinct ipaddresses

count(distinct(s1.ipaddress)) as unique_visits

From statistics, I've added an alias, but that's just out of habit. It is not needed.

FROM statistics s1

Remember you wanted only rows that had event_type 'visit'.
You also had some information need about that, but you did not want to filter those out, you just wanted to see them as separate items in the result.
So the only filter we have is event_type.

WHERE event_type = 'visit'

We group the result by occurrence date, if you leave this line out, you will only see one line. Put it back in to see the difference.

GROUP BY occurrence

And we order the results by number of visits, except I didn't want to rewrite count(distinct(s1.ipaddress)) so I wrote the alias unique_visits; this is allowed (and even encouraged in an order by clause.)

ORDER BY unique_visits;

Links:
Group by, never mind about this myth part, just read the description of how group by works:
http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html
Count: http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

edited Jun 5, 2011 at 13:10

answered Jun 5, 2011 at 12:44

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BebliucGeorge Over a year ago

Works like a charm, thanks. Can you please help me understand how you did it?

BebliucGeorge Over a year ago

Wow, you've put some work into that, thanks a lot. I have another question thought. I want to get only the last 30 days, but in ASC order. So if I ORDER BY occurrence_date DESC LIMIT 30 , i get the last 30, but in DESC order, and if I change it to ASC i get the first 30 entries. Any ideas?

Johan Over a year ago

do: select * from (select .... order by occurrence_date desc limit 30) order by occurrence_date asc;

BebliucGeorge Over a year ago

i have:

SELECT * FROM(SELECT count(*) AS total_visits, COUNT(DISTINCT(s1.ipaddress)) AS unique_visits, occurance_date  FROM statistics s1  WHERE event_type = 'visit' GROUP BY occurance_date ORDER BY occurance_date DESC LIMIT 30) ORDER BY occurance_date ASC;

, but I get Every derived table must have its own alias.

BebliucGeorge Over a year ago

It seems adding an AS statistics in front of the last ORDER BY fixed the error.

Collectives™ on Stack Overflow

MySQL Multiple queries into single one

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related