Query with date period and more

Question

Suppose we have a table named table1:

id name event date 1 name1 f 2010-02-26 21:49:46 2 name2 f 2011-01-21 14:30:26 3 name3 f 2010-05-25 20:51:07 4 name2 r 2011-03-21 21:49:46 5 name4 t 2011-09-15 21:30:26 6 name2 t 2010-01-20 13:07:55 7 name2 t 2011-02-24 20:51:09 8 name1 r 2011-05-20 16:07:55 9 name2 r 2009-07-23 07:51:11 10 name2 r 2011-09-20 21:49:46

A) So I want the result to be in 4 columns:

name f r t name1 f:1 r:1 t:0 name2 f:1 r:3 t:2

B) moreover I want to be order in relation with the bigest sum of f2, r0.5, t*4 DESC:

C) moreover I want to count the number of events only in a specific period for example last week, month, last 6 months. Can you embeded the below SQL query to yor answer? Are there more types of intervals like months years or hours?

SELECT * FROM table1 WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 WEEK) AND CURDATE()

score 1 · Accepted Answer · 2011-09-02 19:35:22Z

You can just use a few self joins (Note: This is untested, since I don't have an installation of MySQL hanging around, but this should work).

select a.name, f,r,t from( select name, count(1) as f,2*count(1) as f_sum from table1 where date >=current_date-30 --or whatever date range you want and event='f' group by name )a join( select name, ,count(1) as r,0.5*count(1) as r_sum from table1 where date >=current_date-30 --or whatever date range you want and event='r' group by name )b on a.name=b.name join( select name, count(1) as t,4*count(1) as t_sum from table1 where date >=current_date-30 --or whatever date range you want and event='t' group by name )c on b.name=c.name order by f_sum+r_sum+t_sum desc;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'text) as f,2*count(1) as f_sum from table1 where date >=current_date-30 ' at line 3
Oops, I goofed with the syntax for cast(). I've made an edit.
sorry another one: #1052 - Column 'name' in field list is ambiguous
Weird...that shouldn't happen since I'm making all of the joins using name. No matter, I've made an edit.
Thanks a lot. 1.One last thing maybe the "cast(count(1) as text)" has to change to "cast(count(*) as unsigned integer)". 2.All I get are "1" in every column and row.

Collectives™ on Stack Overflow

Query with date period and more

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related