sqlsqlitejoingroup-by

Trying to merge groupby results to get a report in single query

I am using sqlite database and have a messages table which is storing who is sending what message to who and at what time. Messages table has following columns

Id number/integer
body string
from uuid
to uuid
inserted_at datetime

what i want is to find out how many messages are exchange between two users.

using following query

    SELECT "from", "to", COUNT(*) AS count
    FROM messages
    GROUP BY "from", "to";

I get this result which is showing two rows for a single chat session due to my simple table structure.
Notice 117d8b9a-b089-45da-8e1c-879108bdbf1a sent 1 total message to 1646471d-63dc-40fc-b4c5-599f9f1e54d0.
In response 1646471d-63dc-40fc-b4c5-599f9f1e54d0 replied with 3 messages.

So i want a query that shows count 4 between user A and B.

From                                    To.                                    Count
117d8b9a-b089-45da-8e1c-879108bdbf1a    1646471d-63dc-40fc-b4c5-599f9f1e54d0    1
1646471d-63dc-40fc-b4c5-599f9f1e54d0    117d8b9a-b089-45da-8e1c-879108bdbf1a    3
1646471d-63dc-40fc-b4c5-599f9f1e54d0    1d7a6815-91ab-4cc3-a1ba-b07e295945e2    1
1d7a6815-91ab-4cc3-a1ba-b07e295945e2    1646471d-63dc-40fc-b4c5-599f9f1e54d0    8

I tried to give as much information I can let me know if you have any questions to understand problem better.

Thanks.

This works for me and i get that from chatgpt.

SELECT 
  CASE 
    WHEN "from" < "to" THEN "from" 
    ELSE "to" 
  END AS user1,
  CASE 
    WHEN "from" < "to" THEN "to" 
    ELSE "from" 
  END AS user2,
  COUNT(*) AS count
FROM messages
GROUP BY user1, user2;

i will mark the solution that right which was suggesting cases and doing magic like this.

Solution

  • Sqlite does not support LEAST and GREATEST but have min(X,Y,...) and max(X,Y,...)

    • The multi-argument max() function returns the argument with the maximum value, or return NULL if any argument is NULL

    • The multi-argument min() function returns the argument with the minimum value

    You could use,

    SELECT  MIN(from_user, to_user) as  fromUsr,
        MAX(from_user, to_user) as  toUsr,
        COUNT(*) AS cnt
FROM  messages
GROUP BY fromUsr , toUsr ;

    Or a CASE expression

    SELECT  CASE WHEN from_user < to_user THEN from_user ELSE to_user END AS fromUsr,
        CASE WHEN from_user > to_user THEN from_user ELSE to_user END AS toUsr,
        COUNT(*) AS cnt
FROM  messages
GROUP BY fromUsr , toUsr;

    See example