In article <[EMAIL PROTECTED]>,
"James KATARSKI" <[EMAIL PROTECTED]> writes:
> I'm trying to generate a report like this: (Which I've done in TWO
> querys, the copied and pasted together)
> +-------------+----------+----------+
> | Page Name | Internal | External |
> +-------------+----------+----------+
> | after_hours | 615 | 105 |
> | conditions | 332 | 50 |
> | faq | 89 | 2 |
> | help | 458 | 174 |
> | labtimes | 682 | 143 |
> | support | 2151 | 383 |
> | passwords | 154 | 22 |
> | practise | 497 | 99 |
> | printing | 801 | 85 |
> | wireless | 926 | 180 |
> +-------------+----------+----------+
> Using a query like this:
> SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as
> 'Internal', COUNT(e.page_name) as 'External'
> FROM ip_logs i, ip_logs e
> WHERE (i.hit_no != e.hit_no)
> AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%')
> #Internal Addresses
> AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%')
> #External Addresses
> GROUP BY i.page_name;
You could try something like
SELECT page_name AS "Page Name",
sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%'
THEN 1 ELSE 0 END) AS "Internal",
sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%'
THEN 0 ELSE 1 END) AS "External"
FROM tst1
GROUP BY page_name
If you have an index on "ip", this should be pretty fast.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
