CODEWITHSUNDEEP
phpmysqldatetimecase
sdoedos
sdoedos

Reputation: 41

Mysql query count certain values

Trying to create a mysql query where it count the names of the filters database. Successful, getting names from a certain period is the main goal.

$query = query("
SELECT count(CASE WHEN filter='冬越しする(現在地検索)' THEN 1 END) as filter, 
count(CASE WHEN filter='開花期で選ぶ' THEN 1 END) as filter2, 
count(CASE WHEN filter='日当たりで選ぶ' THEN 1 END) as filter3,  
count(CASE WHEN filter='株幅で選ぶ' THEN 1 END) as filter4,
count(CASE WHEN filter='背丈で選ぶ' THEN 1 END) as filter5, 
count(CASE WHEN filter='機能、タイプで選ぶ' THEN 1 END) as filter6,
count(CASE WHEN filter='ビギナーおすすめ' THEN 1 END) as filter7 FROM 
filter_clicked WHERE filter_date < DATEADD(day, -30, GETDATE())");

Upvotes: 1

Views: 39

Answers (1)

Fahmi
Fahmi

Reputation: 37473

you've to user now()-now()-interval 30 day - what you've used it is sql server function

$query = query("
SELECT count(CASE WHEN filter='冬越しする(現在地検索)' THEN 1 END) as filter, 
count(CASE WHEN filter='開花期で選ぶ' THEN 1 END) as filter2, 
count(CASE WHEN filter='日当たりで選ぶ' THEN 1 END) as filter3,  
count(CASE WHEN filter='株幅で選ぶ' THEN 1 END) as filter4,
count(CASE WHEN filter='背丈で選ぶ' THEN 1 END) as filter5, 
count(CASE WHEN filter='機能、タイプで選ぶ' THEN 1 END) as filter6,
count(CASE WHEN filter='ビギナーおすすめ' THEN 1 END) as filter7 FROM 
filter_clicked WHERE filter_date < date(now())-interval 30 day")

Upvotes: 2

Related Questions