CODEWITHSUNDEEP
mysqlsql
Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

MySQL - Performing a COUNT(*) for the current row

Here's my SELECT query:

SELECT col1, col3, col5, col8
FROM Table1

In my SELECT query, I want to perform a COUNT(*) exclusively for the current row.

I want something like this, but have no idea how I can get this:

SELECT col1, col3, 
(
SELECT COUNT(*) 
FROM table1 WHERE col3 = col3 value for current row
), 
col5, col8
FROM Table1

What is the correct way to perform a COUNT(*) for the current row of a SELECT query resultset?

Upvotes: 3

Views: 552

Answers (3)

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

Found it.

If I write my query like this:

SELECT col1, col3, 
COUNT(*) AS count,
col5, col8
FROM Table1

then I get COUNT of all the items in Table1 for all rows.

I actually wanted COUNT of all items for selective rows.

Like I may have col3 value appearing more than once. I actually want the count of all items for each col3.

So, GROUP BY clause is the solution.

SELECT col1, col3, 
COUNT(*) AS Count, 
col5, col8
FROM Table1 
GROUP BY col3

Sorry, I think I wasn't able to explain my problem properly, so it confused a lot of people.. A very poor question. My bad.

Upvotes: 1

Jay Ehsaniara
Jay Ehsaniara

Reputation: 1529

try this:

set @num := 0;
SELECT @num := @num+1 , col1, col3, col5, col8 FROM Table1

or other way:

SET @num := ( SELECT COUNT( * ) FROM Table1) ;
SELECT @num := @num -1 , col1, col3, col5, col8
FROM Table1

Upvotes: 2

Saravana Kumar
Saravana Kumar

Reputation: 3729

SELECT col1, col3, 
(
    SELECT COUNT(*) 
    FROM table1 WHERE id = A.Id
) Count, 
col5, col8
FROM Table1 A

Upvotes: 1

Related Questions