CODEWITHSUNDEEP
phpmysql
Grant
Grant

Reputation: 2441

How do I combine these 3 queries into one MySQL query?

Each of the below queries gives me exactly what I want as a result individually, but I want to combine them into one query.

I want to output 3 colums: $row->day, $row->otherPageCount, $row->indexCount

There is one table tracking that contains all the data.

1). 

SELECT COUNT(`page`) AS indexCount FROM `tracking`
WHERE `page` = 'index.php'
AND `date`
BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY)
AND NOW()
GROUP BY DATE(`date`)

2). 

SELECT COUNT(`page`) AS otherPageCount FROM `tracking`
WHERE `page` != 'index.php'
AND `date`
BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY)
AND NOW()
GROUP BY DATE(`date`)

3). 

SELECT DATE(`date`) AS day FROM `tracking`
WHERE `date`
BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY)
AND NOW()
GROUP BY DATE(`date`)

Upvotes: 0

Views: 72

Answers (2)

Konerak
Konerak

Reputation: 39763

You can use an IF for this:

SELECT DATE(`date`), 
  SUM( IF( `page` = 'index.php', 1, 0 ) ) indexCount, 
  SUM( IF( `page` != 'index.php', 1, 0 ) ) otherPageCount
FROM `tracking`
WHERE `date` BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY) AND NOW()
GROUP BY DATE(`date`)

juergen's answer does the same thing, but makes the IF implicit. If you understand how it works, that's the better answer.

Upvotes: 2

juergen d
juergen d

Reputation: 204746

SELECT DATE(`date`),
       COUNT(`page`) AS indexCount, 
       SUM(`page` = 'index.php') as idx_count, 
       SUM(`page` <> 'index.php') as not_idx_count
FROM `tracking`
WHERE `date` BETWEEN DATE_ADD(NOW(),INTERVAL -30 DAY) AND NOW()
GROUP BY DATE(`date`)

Upvotes: 4

Related Questions