CODEWITHSUNDEEP
sqldatabasepostgresql
StaphWar
StaphWar

Reputation: 57

How to count the unique rows after aggregating to array

Trying to solve the problem in a read-only manner.

My table (answers) looks like the one below:

|    user_id     |    value    |   
+----------------+-------------+
|       6        |    pizza    |       
|       6        |    tosti    | 
|       9        |    fries    | 
|       9        |    tosti    |
|      10        |    pizza    | 
|      10        |    tosti    |
|      12        |    pizza    |
|      12        |    tosti    |
|      13        |    sushi    |  -> did not finish the quiz.

NOTE: the actual table has 15+ different possible values. (Answers to questions).

I've been able to make create the table below:

|    value arr   |     count    |  user_id  |   
+----------------+--------------+-----------+
|  pizza, tosti  |       2      |     6     |
|  fries, tosti  |       2      |     9     |
|  pizza, tosti  |       2      |     10    |*
|  pizza, tosti  |       2      |     12    |*    
|     sushi      |       1      |     13    |

I'm not sure if the * rows show up in my current query (DB has 30k rows and 15+ value options). The problem here is that "count" is counting the number of answers and not the number of unique outcomes.

Current query looks a bit like:

select string_agg(DISTINCT value, ',' order by value) AS value, user_id,
COUNT(DISTINCT value)
FROM answers
GROUP BY user_id;

Looking for the unique answer combinations like the table shown below:

|    value arr   | count unique |   
+----------------+--------------+
|  pizza, tosti  |       3      |       
|  fries, tosti  |       1      |
|     sushi      |       1      | --> Hidden in perfect situation.

Tried a bunch of queries, both written and generated by tools. From super simplified to quite complex, I keep ending up with the answers being count instead of the unique combination accros users.

If this is a duplicate question, please re-direct me to it. Learned a lot these last few days, but haven't been able to find the answer yet.

Any help would be highly appreciated.

Upvotes: 1

Views: 149

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

Here's what you need. Your almost there.

select t1.value, count(1) From (
  select string_agg(DISTINCT value, ',' order by value) AS value, user_id
  FROM answers
  GROUP BY user_id) t1
group by t1.value;

Upvotes: 1

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

You can try (this is for SQL Server):

select count(*), string_agg(value, ",")
within group (order by value) as count_unique
from answers
group by string_agg(value, ",")

Upvotes: 1

Related Questions