Count function using two columns in sql table

Question

PICTURE OF SQL TABLE PLEASE LOOK

I'd like to count the number of uris that have xml in it for each request_ip, so id like the results table to look like this

| Request ip | Number of hits |
| 34.212.134 | 37298191       |

This is the query I tried but it didn't work

SELECT request_ip 
FROM "default"."www-live-lon-cf" 
COUNT (uri)
WHERE date>=date('2022-08-20')
AND date<=date('2022-10-31')
AND uri LIKE '%.xml'
group by request_ip

Answer 1

You need to place count into the select statement:

SELECT request_ip, COUNT(*) hit_count
FROM "default"."www-live-lon-cf" 
WHERE date>=date('2022-08-20')
AND date<=date('2022-10-31')
AND uri LIKE '%.xml'
group by request_ip

If you want "human-readable" column names you can use double quotes for them:

SELECT request_ip as "Request ip", COUNT(*) as "Number of hits"