Counting entries using a sub-query in SQL-CodePudding

commercial	clicks
1	0
2	1
2	1
3	1
4	0
5	0
5	0
6	1
7	1
7	1
8	0
9	1
9	1
10	0

The table described above (the name of the table is a table) has 2 columns. The commercial column shows the ads shown to the user (the chronological order is irrelevant). The clicks column shows whether a user who saw the link clicked on it or not (0 - did not click, 1 - clicked).

I need to make a SQL query for how many advertisements there are upon clicking one, two clicks, three clicks, etc. (so for every number of clicks in the table), using a sub-query.

Thank you.

SELECT  commercial, SUM(click)
FROM     table
GROUP BY commercial 
ORDER BY commercial ASC

commercial	clicks
1	0
2	2
3	1
4	0
5	0
6	1
7	2
8	0
9	2
10	0

I made this so far but then i get stuck on how to make a sub-query to calculate how many commercials are clicked once or 2 times. My expected result is:

clicks	commercial
0	1,4,5,8,10
1	3,6
2	2,7,9
3

I'm also wondering if i could get this result

commercial	clicks
1	0
2	2
3	1
4	0
5	0
6	1
7	2
8	0
9	2
10	0

Without using the SUM and GROUP BY function, please let me know if thats possible.

CodePudding user response：

You need to compute a further aggregation and use BigQuery STRING_AGG for string aggregation.

SELECT num_clicks, 
       STRING_AGG(CAST(commercial AS STRING)) AS commercials
FROM (SELECT  commercial, 
              SUM(clicks) AS num_clicks
      FROM     tab
      GROUP BY commercial) clicks_per_commercial 
GROUP BY num_clicks

"Without using the SUM and GROUP BY function, please let me know if thats possible.": no, you can't bypass the summing aggregation step.

CodePudding user response：

Select clicks, string_agg(commercial::varchar,',' order by commercial asc) as commercials
 from table 
group by clicks 
order by clicks asc;