CODEWITHSUNDEEP
mysqlsqlcountgroup-by
Ashish
Ashish

Reputation: 303

Count rows using group by and display all rows MYSQL PHP

I have a sql table like this:

id     fname     cat                  address        status

1      Bash      Wedding venue        ABC            a
2      Bash      Wedding venue        BCD            a
3      jash      Wedding venue        ABC            a
4      hash      Wedding venue        BCD            a   
5      Rash      Wedding card         BCD            a

I want to fetch all the results having cat value Wedding venue and count duplicate fname. The query I am using is this, and its working fine.

SELECT *, count(*) as counts
from table
where cat='Wedding venue' AND status='a'
Group by fname;

Output:

id     fname   count(*)     cat                address      status
1      Bash    2            Wedding Venue      ABC          a
3      jash    1            Wedding Venue      ABC          a
4      hash    1            Wedding Venue      BCD          a

Is there any possible way to display output like this:

id     fname   count(*)     cat                address      status
1      Bash    2            Wedding Venue      ABC          a
2      Bash    2            Wedding Venue      BCD          a
3      jash    1            Wedding Venue      ABC          a
4      hash    1            Wedding Venue      BCD          a

Upvotes: 2

Views: 1767

Answers (2)

jarlh
jarlh

Reputation: 44766

You can use a correlated sub-query to count number of same fnames:

SELECT t1.*, (select count(*) from table t2
              where t2.fname = t1.fname) as counts 
from table t1
where cat='Wedding venue' AND status='a'

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

We have different definitions of 'working fine', so it may be a little tricky for me to help.

But a query like this could get the result you're after.

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,fname VARCHAR(12) NOT NULL
,cat VARCHAR(20) NOT NULL
,address VARCHAR(12) NOT NULL
,status CHAR(1) NOT NULL
);

INSERT INTO my_table VALUES
(1,'Bash','Wedding venue','ABC','a'),
(2,'Bash','Wedding venue','BCD','a'),
(3,'jash','Wedding venue','ABC','a'),
(4,'hash','Wedding venue','BCD','a'),
(5,'Rash','Wedding card','BCD','a');

SELECT x.*
     , y.count 
  FROM my_table x 
  JOIN 
     ( SELECT fname
            , cat
            , status
            , COUNT(*) count 
         FROM my_table 
        GROUP 
           BY fname
            , cat
            , status
     ) y 
    ON y.fname = x.fname 
   AND y.cat = x.cat 
   AND y.status = x.status 
 WHERE x.cat = 'Wedding venue' 
   AND x.status = 'a';
+----+-------+---------------+---------+--------+-------+
| id | fname | cat           | address | status | count |
+----+-------+---------------+---------+--------+-------+
|  1 | Bash  | Wedding venue | ABC     | a      |     2 |
|  2 | Bash  | Wedding venue | BCD     | a      |     2 |
|  3 | jash  | Wedding venue | ABC     | a      |     1 |
|  4 | hash  | Wedding venue | BCD     | a      |     1 |
+----+-------+---------------+---------+--------+-------+

Upvotes: 1

Related Questions