CODEWITHSUNDEEP
mysqlsql
Ihor Sh
Ihor Sh

Reputation: 106

select and count rows

I have 2 tables and I need to select and count rows in one query, maybe somebody can help me with that, my query is: 

SELECT
    c.id, c.first_name, c.last_name, c.speciality, c.level, c.email, c.skype, c.city, 
    s.status_type, c.status_id, c.linkedin, c.link_cv, c.interview_res, c.createdAt,
    c.updatedAt, c.recruiter_id, u.first_name AS fname, u.last_name AS lname
FROM
    Candidates c
    JOIN Users u 
        ON c.recruiter_id = u.id
    JOIN Statuses s
        ON s.id = c.status_id
WHERE
    c.deleted = false

and I need to get count of the rows that respond my select and count them.

example output (what I want):

count | fname  | lname  | ..... |
---------------------------------
3     | Ihor   | Shmidt | ...   |
3     | Andre  | Agassi | ....  |
3     | Roger  | Federer| ..... |

i.e. I want to have my fields that I select and their count

Upvotes: 0

Views: 188

Answers (3)

Ihor Sh
Ihor Sh

Reputation: 106

SELECT c.id, ( SELECT COUNT(*) FROM Candidates c 
JOIN Users u ON c.recruiter_id = u.id 
JOIN Statuses s ON s.id = c.status_id 
WHERE c.deleted = false ) AS count  FROM Candidates 
c JOIN Users u ON c.recruiter_id = u.id 
JOIN Statuses s ON s.id = c.status_id 
WHERE c.deleted = false ;

Upvotes: 0

Vanya Avchyan
Vanya Avchyan

Reputation: 880

COUNT(*) as count or COUNT(c.id) as count

SELECT COUNT(*) as count,c.id, c.first_name, c.last_name, c.speciality, c.level, c.email,
    c.skype, c.city, s.status_type, c.status_id, c.linkedin, c.link_cv, c.interview_res,
    c.createdAt, c.updatedAt, c.recruiter_id, u.first_name AS fname, u.last_name AS lname 
  FROM Candidates c 
  JOIN Users u
    ON c.recruiter_id = u.id
  JOIN Statuses s
    ON s.id = c.status_id
  WHERE c.deleted = false;

Upvotes: 0

Srid
Srid

Reputation: 11

Before the "from" portion of the query, please add count (*). It will give you the count of the rows.

Select ......, count (*) from Candidates c 
JOIN Users u ON c.recruiter_id = u.id 
JOIN Statuses s on s.id = c.status_id
WHERE c.deleted = false;

Upvotes: 1

Related Questions