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
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
I would create a column for your legtype values and then count grouping by those :
You just need to adapt the case to create legtype with your other values.