CODEWITHSUNDEEP
mysqlsql
user2527874
user2527874

Reputation: 11

how to get row count in a table of different types in one table within same query

For Example,the table contains the column jobtype, the thing is we need to count no. of rows of same jobtype and at the same time for all the jobs in same query.

Upvotes: 1

Views: 139

Answers (3)

Ahmed
Ahmed

Reputation: 450

try this:

SELECT COUNT(*) AS same_duplicate_jobs ,job_type ,total_jobs = ( SELECT    COUNT(*) FROM      jobs) FROM      jobs GROUP BY  job_type HAVING    COUNT(*) > 1

Upvotes: 0

user2168999
user2168999

Reputation: 1

You could create 2 int variables

$SQL = mysql_query('SELECT * FROM table');
$jobLookingFor = 'programmer';
$jobTypeSameCount = 0;
$jobTotalNumber = 0;

while($result =  mysql_fetch_array($SQL))
{
  if($result['jobtype'] === $jobLookingFor)
  {
    $jobTypeSameCount = $jobTypeSameCount + 1;
  }

  $jobTotalNumber = $jobTotalNumber + 1;
}

That is off the top of my head, so excuse the spelling mistakes.

after the while loop has completed the jobTypeSameCount will be the number of the same jobs you are searching for the jobTotalNumber will be the total rows including both the similar and other job type rows.

EDIT: Try this solution from a simillar question

SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*) FROM MyTable GROUP BY COL_1, COL_2, COL_3, COL_4

If you ever want to weed out rows that don't have a duplicate:

SELECT COL_1, COL_2, COL_3, COL_4, COUNT() FROM MyTable GROUP BY COL_1, COL_2, COL_3, COL_4 HAVING COUNT() > 1

at: How to find duplicate count among several columns?

Upvotes: 0

Abubakkar
Abubakkar

Reputation: 15664

you can try this :

select jobtype, count(*) from your_table group by jobtype 
  union 
select 'My Total', count(*) from your_table;

This will give result like(based on your table):

IT         5
Admin      10
Myv Total  15

Upvotes: 2

Related Questions