开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜