CODEWITHSUNDEEP
phpmysql
Linesh jose
Linesh jose

Reputation: 53

How to get row counts from a table by different conditions

I've a table named "posts" with fields id,post,status etc.. I want to create a query to get post count by the post's status like this

------------------------------
| published | draft  | trash | 
------------------------------
| 150       |  15    |  0    |
------------------------------

Please help me :

Upvotes: 0

Views: 78

Answers (5)

Deepak Kumar
Deepak Kumar

Reputation: 92

The perfect solution, As you display :

**

SELECT SUM(main_a.published) as published, SUM(main_a.draft) as draft, SUM(main_a.trash) as trash FROM (SELECT 
      IF (`status`= 'published' ,COUNT(*), 0 ) AS published, 
      IF (`status`= 'draft' , COUNT(*) , 0) AS draft,
      IF (`status`= 'trash', COUNT(*) , 0) AS trash  FROM posts GROUP BY `status` ) main_a

**

Note your table structure should as follows :

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post` text,
  `status` enum('published','draft','trash') DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Upvotes: 0

Deepak Kumar
Deepak Kumar

Reputation: 92

SELECT SUM(main_a.published) as published, SUM(main_a.draft) as draft, SUM(main_a.trash) as trash FROM (SELECT IF (status= 'published' ,COUNT(), 0 ) AS published, IF (status= 'draft' , COUNT() , 0) AS draft, IF (status= 'trash', COUNT(*) , 0) AS trash FROM posts GROUP BY status ) main_a

Upvotes: 0

user1502852
user1502852

Reputation:

SELECT published , COUNT(*) FROM posts WHEREpublished = 150;

OR

SELECT published , COUNT(*) FROM posts;

Upvotes: 1

Chelseawillrecover
Chelseawillrecover

Reputation: 2644

SELECT   status, COUNT(*)
FROM     posts
GROUP BY status

Upvotes: 1

Paul Draper
Paul Draper

Reputation: 83235

SELECT status, COUNT(*) count FROM posts GROUP BY status

That is more typical, and will give you the following.

-----------------------
| status    | count   | 
-----------------------
| published |  150    |
-----------------------
| draft     |  15     |
-----------------------
| trash     |  0      |
-----------------------

If you want exactly the table you asked for, do

SELECT SUM(CASE status WHEN 'published' THEN 1 ELSE 0 END) published,
    SUM(CASE status WHEN 'draft' THEN 1 ELSE 0 END) draft,
    SUM(CASE status WHEN 'trash' THEN 1 ELSE 0 END) trash
FROM posts

Upvotes: 1

Related Questions