How do I can count by SQL ? ( chat system billboard )

Question

4.00/5 (1 vote)

See more:

[ SQL statement ]
Hello Coders !

A tiny challenge to achieve :
the table in question : "CONVERSATION"
the fields :
ID (auto inc ) | ORIGIN ( int ) | TARGET ( int )| TEXT ( text )
================================================================

this table store conversations between 2 users only ( it's 1 to 1 chat only )
ORIGIN and TARGET fields are linked to USER table ( it's foreign keys ) for both people talking.

the sql statement that I try to write permit to count all the conversations stored in the table conversation.

What I have tried:

I am at this point as first try :

"SELECT COUNT(DISTINCT TARGET) AS TOTAL FROM CONVERSATION GROUP BY ORIGIN"

I have to know if I'm relevant or not...
and look for the best or shortest sql statement for this

Any ideas ?
Thanks in advance ! ;)

Posted 27-Jun-19 23:23pm

User 14076552

Updated 2-Jul-19 21:29pm

Add a Solution

Comments

CHill60 28-Jun-19 5:24am

Run your code and see what happens. What is the problem?

3 solutions

Solution 3

And if you are looking for the count between 2 particular users...

SQL

SELECT Count(*)
FROM   Conversation
WHERE  (Origin = @User1 AND [Target] = @User2)
OR     (Origin = @User2 AND [Target] = @User1)

Posted 28-Jun-19 1:40am

MadMyche

Comments

Maciej Los 28-Jun-19 8:51am

Good one!

MadMyche 28-Jun-19 9:54am

Thank you; couldn't quite tell what the poster wanted.

[no name] 29-Jun-19 6:41am

yes, I use this request with "select *" to load the conversation between two users.
thanks ;)

the needed request will count the existing conversation,
that's why I count the distinct target and group by origin
as there is never a blank field in the table, rows are full or are not created

Solution 4

Hi all !
Good News !
I succeed to avoid redondant ORIGIN and TARGET the following way :
a sub request is needed :

SELECT COUNT(DISTINCT TARGET) AS TOTAL FROM CONVERSATION WHERE ORIGIN IN (SELECT DISTINCT TARGET FROM CONVERSATION)

It's accurate now !

Thanks all !

Posted 2-Jul-19 21:30pm

User 14076552

Comments

CHill60 3-Jul-19 5:40am

So you only wanted to count target conversations where the originator had been the target of a conversation - i.e. not counting conversations where the someone had originated a conversation and had not (yet) received a reply. Interesting scenario.

Maciej Los 3-Jul-19 15:01pm

If it resolves your issue, please mark this answer as a solution to remove your question from unanswered list.
5 for your effort.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2019-06-28T00:09:00

Solution 2

Quote:
the sql statement that I try to write permit to count all the conversations stored in the table conversation

If you want to count all conversations:

SQL

SELECT COUNT(*)  AS CountOfAll
FROM Conversation;

If you want to count all conversations on Origin field:

SQL

SELECT Origin, COUNT(*) AS CountOfOrigin
FROM Conversation
GROUP BY Origin;

If you want to count all conversations on Target field:

SQL

SELECT [Target], COUNT(*) AS CountOfTarget
FROM Conversation
GROUP BY [Target];

For furter details, please see: MySQL :: MySQL 8.0 Reference Manual :: 3.3.4.8 Counting Rows[^]

Posted 28-Jun-19 0:09am

Maciej Los

Updated 28-Jun-19 0:10am

v2

Comments

MadMyche 28-Jun-19 9:55am

+5 (3 hours ago)

Maciej Los 28-Jun-19 10:02am

Thank you.
I guessed that was you ;)

[no name] 29-Jun-19 6:51am

the "select COUNT(*) AS CountOfAll FROM Conversation" don't count all the conversation, but all the rows in the "conversation" table.
what I am looking for is to count all the conversation, knowing a conversation is between user1 and user2, and origin and target can be user1 to user2, or user2 to user1.
Thank you Maciej Los

Maciej Los 30-Jun-19 6:33am

Seems, @MadMyche has guessed your intention.