How to get row count in mySql database
+-------------------+---------------------+------+-----+---------+----------------+
| RowId | MSGID| Received| UID | Default | |
+-------------------+---------------------+------+-----+---------+----------------+
| 1 | 1 | 开发者_C百科NO | 1 | NULL | |
| 2 | 1 | YES | 3 | NULL | |
| 3 | 1 | YES | 4 | NULL | |
| 4 | 1 | YES | 5 | NULL | |
| 5 | 5 | YES | 2 | NULL | |
| 6 | 2 | YES | 8 | NULL | |
| 7 | 1 | YES | 9 | NULL | |
+-------------------+---------------------+------+-----+---------+----------------+
This is my table in MySQL.
How can I get count of messages where MSGID = 1
and count of no of users who have received message where MSGID = 1
and count of no of users who haven't received message where MSGID = 1
?
I want to do it in a loop so that I can Get three values like [5,4,1]. So each time the page load the query will check the database and update the sets. pls help
First to get a count for the number of messages where MSGID = 1
SELECT COUNT(RowID) FROM tableName WHERE MSGID = 1
To get a count of the number of users who have received a msg where MSGID = 1
SELECT COUNT(DISTINCT UID) FROM tableName WHERE MSGID = 1
To get a count of the number of users who haven't received a msg where MSGID = 1
SELECT COUNT(DISTINCT UID) FROM tableName WHERE MSGID =1 AND Received = 'NO'
You can do this in one query. I don't see the benefit of the loop?
select count(*)
, count(distinct uid)
, count(distinct case when received = 'NO' then uid else null end case)
from table_name
where msgid = 1
This should really be multiple tables,
1) users, distinct on uid
2) messages, distinct on msgid, with received yes / no.
To get it using one query you can do a union all like the following:
SELECT COUNT(RowID) AS 'Col1', '' AS 'Col2', '' AS 'Col3'
FROM tableName WHERE MSGID = 1
UNION ALL
SELECT '' AS 'Col1', COUNT(DISTINCT UID) AS 'Col2', '' AS 'Col3'
FROM tableName WHERE MSGID = 1
UNION ALL
SELECT '' AS 'Col1', '' AS 'Col2' , COUNT(DISTINCT UID) AS 'Col3'
FROM tableName WHERE MSGID =1 AND Received = 'NO'
Hope this works out for you.
精彩评论