CODEWITHSUNDEEP
postgresqlcount
Nithin P.H
Nithin P.H

Reputation: 732

Finding top searched country and ip from a table

I have a table "user" with columns ip,os,country and browser. I want to find the ip,os,country and browser with maximum count.Is there any query for that in PostgreSQL

The current query I'm using is

SELECT *
FROM
(
SELECT COUNT(ip),ip FROM user GROUP BY ip 
UNION ALL
SELECT COUNT(os),os FROM user GROUP BY os 
UNION ALL
SELECT COUNT(country),country FROM user GROUP BY country 
UNION ALL
SELECT COUNT(browser),browser FROM user GROUP BY browser
) user

it shows all ip,os,country and browser and their count what i really want is a column name the max count of that column

is it possible to do that in a single query?

Im expecting something like this

os      count           ip              count
linux     50         xx:xx:xx:xx      95

Upvotes: 0

Views: 52

Answers (2)

Abelisto
Abelisto

Reputation: 15624

SELECT *
FROM
  (SELECT COUNT(ip) as cnt_ip, ip FROM user GROUP BY ip ORDER BY 1 DESC LIMIT 1) as t_ip,
  (SELECT COUNT(os) as cnt_os, os FROM user GROUP BY os ORDER BY 1 DESC LIMIT 1) as t_os,
  (SELECT COUNT(country) as cnt_country, country FROM user GROUP BY country ORDER BY 1 DESC LIMIT 1) as t_country,
  (SELECT COUNT(browser) as cnt_browser, browser FROM user GROUP BY browser ORDER BY 1 DESC LIMIT 1) as t_browser

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10701

You may use HAVING and ALL for that. Due to readability purpose, I'll show just for one column

SELECT COUNT(ip),ip 
FROM user 
GROUP BY ip 
HAVING COUNT(ip) >= all
(
  SELECT COUNT(ip)
  FROM user 
  GROUP BY ip 
)

Upvotes: 1

Related Questions