Let’s assume I have a table like this:

In this table, some IP addresses are associated with some domains and we can consider them being single URL. (For example, domain2.com, 10.100.15, 11.22.33 - it's domain2.com; 172.100.15, domain3.com - domain3.com, etc). My task is that I have to calculate rows with different domains and make a table like this:

I could just count them with several queries, such as:

SELECT COUNT(*) 
FROM table1
WHERE (table1.url = “10.100.15”
    OR table1.url = “11.22.33” OR table1.url = “domain2.com”)

and make this table manually but it’s not cool.

How could I do it in a single query?

PS: as mentioned in the comment below, usual aggregation could be done with one simple query:

SELECT url, count(*) 
FROM table1
WHERE GROUP BY url

But it gives me only the table like this:

Or am I missing something?

CodePudding user response：

You can group by a CASE expression:

SELECT CASE 
         WHEN URL IN ('10.100.15', '11.22.33', 'domain2.com') THEN 'domain2.com'
         WHEN URL IN ('172.100.15', 'domain3.com') THEN 'domain3.com'
         ELSE URL
       END domain,
       COUNT(*) count
FROM tablename
GROUP BY domain;

See the demo.

CodePudding user response：

You should use group by function and write your code like this:

SELECT URL, count(*) from table1 where URL in('domain1','domain2') group by (URL)

it will work!

CodePudding user response：

A more elegant way is to have the association stored in the database to achieve that:

WITH
-- your input ...
indata(Id,url) AS (
          SELECT 1,'11.22.33'
UNION ALL SELECT 2,'11.22.33'
UNION ALL SELECT 3,'domain.com'
UNION ALL SELECT 4,'domain2.com'
UNION ALL SELECT 5,'domain.com'
UNION ALL SELECT 6,'10.100.15'
UNION ALL SELECT 7,'domain3.com'
UNION ALL SELECT 8,'172.100.15'
UNION ALL SELECT 9,'172.100.15'
)
,
-- need an association table - which "url" belongs to which domain
assoc(dom,url) AS (
            SELECT 'domain2.com','10.100.15'
  UNION ALL SELECT 'domain2.com','11.22.33'
  UNION ALL SELECT 'domain3.com','172.100.15'
)
SELECT
  CASE 
    WHEN a.dom IS NULL THEN i.url
    ELSE a.dom
  END AS domain
, COUNT(*) AS counter
FROM indata AS i
LEFT
JOIN assoc  AS a USING(url)
GROUP BY domain
;

-- out
-- out    domain    | counter
-- out ------------- ---------
-- out  domain2.com |       4                                                                                                                                                                             
-- out  domain3.com |       3
-- out  domain.com  |       2
-- out (3 rows)