Create frequent number sqlite queries

Good evening. I created a database with 9 columns and 605 rows. On column 3, 4, 5, 6, 7 there are numbers ranging from 1 to 90 random. Now I would like to create a query that tells me on each line how many times a number is frequent. The table is divided into 11 lines: Bari, Cagliari etc. For each city I have to count how many times a number has come out.

I await your response. Thanks.

Link a image: enter link description here

Answer

When you post SQL questions, it often helps to tell us what database system you’re using, because the syntax is often slightly different between systems.

However, here is a query which (I think) does what you expect for most systems. You might have to tweak it.

WITH CTE_ESTR1 AS (
    SELECT ESTR1 AS NUM, RUOTA, COUNT(1) AS CNT
    FROM {{ your_table }}
    GROUP BY 1, 2
)
,CTE_ESTR2 AS (
    SELECT ESTR2 AS NUM, RUOTA, COUNT(1) AS CNT
    FROM {{ your_table }}
    GROUP BY 1, 2
)
,CTE_ESTR3 AS (
    SELECT ESTR3 AS NUM, RUOTA, COUNT(1) AS CNT
    FROM {{ your_table }}
    GROUP BY 1, 2
)
,CTE_ESTR4 AS (
    SELECT ESTR4 AS NUM, RUOTA, COUNT(1) AS CNT
    FROM {{ your_table }}
    GROUP BY 1, 2
)
,CTE_ESTR5 AS (
    SELECT ESTR5 AS NUM, RUOTA, COUNT(1) AS CNT
    FROM {{ your_table }}
    GROUP BY 1, 2
)
,CTE_ALL AS (
    SELECT * FROM CTE_ESTR1 
    UNION ALL SELECT * FROM CTE_ESTR2 
    UNION ALL SELECT * FROM CTE_ESTR3 
    UNION ALL SELECT * FROM CTE_ESTR4 
    UNION ALL SELECT * FROM CTE_ESTR5 
)
SELECT NUM, RUOTA, SUM(CNT) AS NUM_OCCURENCE
FROM CTE_ALL
GROUP BY NUM, RUOTA;

I generated it with Rasgo automatically and then tested it in Snowflake.