Plz Help

esemerda · February 24th, 2005, 10:13 PM

Here's my dummy table:
page ip mark
pageA 127.0.01 n
pageA 127.0.01 c
pageB 128.42.52 n
pageB 128.42.52 c
pageB 128.42.52 c
pageB 328.3.11 n
pageB 328.3.11 c
pageC 228.2.01 n

i'd like to run a count(distinct ip), group by page but also be able to extract how many distinct by page using the mark column where char ='n'.

ie. i need 1 sql statement which will pump the following out;

page count(ip) count(mark)
pageA 2 1
pageB 3 2
pageC 1 1

total 6 4

any ideas?
Cheers,
Ernest

pgtips · February 25th, 2005, 05:14 AM

I think this will do it:

Code:

SELECT page, 
       COUNT(ip) AS count_ip,
       SUM(CASE WHEN mark='n' THEN 1 ELSE 0 END) AS count_mark
FROM <<table>>
GROUP BY page

esemerda · February 28th, 2005, 06:41 AM

Quote:

quote:Originally posted by pgtips
I think this will do it:

Code:

SELECT page, 
       COUNT(ip) AS count_ip,
       SUM(CASE WHEN mark='n' THEN 1 ELSE 0 END) AS count_mark
FROM <<table>>
GROUP BY page

Thank you pgtips. It worked well!