skip to Main Content

I want to identify and count uris that contain the following values

ukpga
ukla
asp
asc
anaw
mwa
ukcm
nia
aosp
aep
aip
apgb
nisi
mnia
apni

I have a column in the table that has a list of uris.

The query below is what I have done so far – this works but it will become a very long query so I wanted to find a way to shorten it.

SELECT 'uksi' AS legtype,
 COUNT (uri)  
FROM "default"."www-live-lon-cf" 
WHERE date >= date('2023-02-01')
  AND date <= date('2023-08-31')
  AND uri LIKE '%.uksi'
UNION
SELECT 'ukpga' AS legtype,
 COUNT (uri)  
FROM "default"."www-live-lon-cf" 
WHERE date >= date('2023-02-01')
  AND date <= date('2023-08-31')
  AND uri LIKE '%.ukpga'

2

Answers


  1. I would suggest you to create a procedure if you have to do the same query for a lot of uris.

    To create a procedure I suggest you to follow this link

    Login or Signup to reply.
  2. I would create a column for your legtype values and then count grouping by those :

    WITH regrouped_uri AS(
    
    SELECT CASE
                WHEN uri LIKE '%.ukpga' THEN 'ukpga'
                WHEN uri LIKE '%.uksi' THEN 'uksi'
                ELSE null
            END AS legtype,
            uri
    FROM "default"."www-live-lon-cf"
    
    WHERE date >= date('2023-02-01') 
      AND date <= date('2023-08-31')
    )
    
    SELECT legtype,
            COUNT(uri) AS count_uri
    FROM regrouped_uri
    GROUP BY legtype
         
    

    You just need to adapt the case to create legtype with your other values.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search