CODEWITHSUNDEEP
phpsqlsqlitecountdistinct
Anna K.
Anna K.

Reputation: 1995

How to get row count as column

So I have a query like this

SELECT DISTINCT colB FROM tbl WHERE colA = "foo" LIMIT 0, 20

which gets me an array with max. 20 records where colA matches "foo", but without duplicate records. Each array entry contains an associative array that contains the value of colB, like

0 => array(
       'colB' => 'some_value',
     ),
1 => array(
       'colB' => 'other_value',
     )

Can I also get the COUNT() for colB value? I mean this:

SELECT COUNT() as cbCount FROM tbl WHERE colA = "foo" AND colB = "some_value"
SELECT COUNT() as cbCount FROM tbl WHERE colA = "foo" AND colB = "other_value"

But included in the first query as another array field, like:

0 => array(
       'colB' => 'some_value',
       'cbCount' => 5,
     ),
1 => array(
       'colB' => 'other_value',
       'cbCount' => 2,
     )

Upvotes: 1

Views: 71

Answers (2)

MD Sayem Ahmed
MD Sayem Ahmed

Reputation: 29166

You need to use Group By with Count - 

SELECT 
        colB, count(colB) as cbCount
FROM    
        tbl 
WHERE  
        colA = 'foo' 
GROUP BY 
        colB 
LIMIT 
        0, 20

This query will fetch the first 20 rows, group them according to the distinct values of colB and give their count.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

all you need to do is to use GROUP BY clause

SELECT  colB,
        COUNT(colB) AS cbCount
FROM    tableName
WHERE   colA = 'Foo'
GROUP   BY colB

Upvotes: 1

Related Questions