SQL & PL/SQL

Hi,

what was the query you used ?
It could be some thing like this.

---untested

SELECT COUNT(*) people, visited FROM
(
SELECT COUNT(*) visited , name FROM
TABLE1
GROUP BY NAME
) GROUP BY visited

Thanks

What I tried was

select distinct name, count(name) over (partition by name order by name) from visitors

but got a count of how many times each person visited - not quite what I had in mind.

When I tried your suggestion I got exactly what I needed

Guess I was trying to over complicate things. Your suggestion was short and concise. Very nice

Thanks
Wayne

select    count(*)
          || ' '
          || case when abs(count(*)) = 1 then 'person' else 'people' end
          || ' visited ' || num_visits || ' time'
          || case when abs(num_visits) = 1 then '.' else 's.' end
          result
from      (
              select  person
              ,       count(*) num_visits
              from    table
          )
group by  num_visits;

note: untested

Try this!

WITH data_Set AS
(
select 'Bill' people_name FROM dual UNION ALL
SELECT 'Fred' people_name FROM dual UNION ALL
SELECT 'Bob' people_name FROM dual UNION ALL
SELECT 'Jim' people_name FROM dual UNION ALL
SELECT 'Jim' people_name FROM dual UNION ALL
SELECT 'Erica' people_name FROM dual UNION ALL
SELECT 'Erica' people_name FROM dual UNION ALL
SELECT 'Sam' people_name FROM dual UNION ALL
SELECT 'Sam' people_name FROM dual UNION ALL
SELECT 'Sam' people_name FROM dual UNION ALL
SELECT 'Julie' people_name FROM dual UNION ALL
SELECT 'Julie' people_name FROM dual UNION ALL
SELECT 'Julie' people_name FROM dual UNION ALL
SELECT 'Michelle' people_name FROM dual UNION ALL
SELECT 'Michelle' people_name FROM dual UNION ALL
SELECT 'Jack' people_name FROM dual UNION ALL
SELECT 'Jack' people_name FROM dual UNION ALL
SELECT 'Jack' people_name FROM dual UNION ALL
SELECT 'Jack' people_name FROM dual
)
SELECT COUNT(1)||' People Visited '||visit_count||' times'
FROM (
SELECT people_name, COUNT(1) visit_count
FROM data_Set
GROUP BY people_name)
GROUP BY visit_count
ORDER BY visit_count;


Result is as follows:

COUNT(1)||'PEOPLEVISITED'||VISIT_COUNT||'TIMES'
--------------------------------------------------------
3 People Visited 1 times
3 People Visited 2 times
2 People Visited 3 times
1 People Visited 4 times


Regards,

Raj

WITH data_Set AS(
select 'Bill' people_name FROM dual UNION ALL
SELECT 'Fred' people_name FROM dual UNION ALL
SELECT 'Bob' people_name FROM dual UNION ALL
SELECT 'Jim' people_name FROM dual UNION ALL
SELECT 'Jim' people_name FROM dual UNION ALL
SELECT 'Erica' people_name FROM dual UNION ALL
SELECT 'Erica' people_name FROM dual UNION ALL
SELECT 'Sam' people_name FROM dual UNION ALL
SELECT 'Sam' people_name FROM dual UNION ALL
SELECT 'Sam' people_name FROM dual UNION ALL
SELECT 'Julie' people_name FROM dual UNION ALL
SELECT 'Julie' people_name FROM dual UNION ALL
SELECT 'Julie' people_name FROM dual UNION ALL
SELECT 'Michelle' people_name FROM dual UNION ALL
SELECT 'Michelle' people_name FROM dual UNION ALL
SELECT 'Jack' people_name FROM dual UNION ALL
SELECT 'Jack' people_name FROM dual UNION ALL
SELECT 'Jack' people_name FROM dual UNION ALL
SELECT 'Jack' people_name FROM dual)
select distinct to_char(count(*) over(partition by count(*)))
    || 'people visited '
    || to_char(count(*)) || 'times' as visit
  from data_Set
group by people_name;

VISIT
----------------------
2people visited 3times
1people visited 4times
3people visited 2times
3people visited 1times