Ihor Sh Ihor Sh - 5 months ago 20
SQL Question

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 utpul( 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

Answer

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;