CODEWITHSUNDEEP
mysqlsql
divya kodeeswaran
divya kodeeswaran

Reputation: 37

Having issue in my count function

I want to count how many data available are available. This is what I have:

SELECT 
    count('pincode'), tr.*, t.* 
FROM tinfo t 
LEFT JOIN tutorregistration tr ON tr.tid = t.tsid 
WHERE pincode LIKE '%andhra%';

This gives the result count('pincode') = 11 and stops at only 1 row record. How can I get all records from my query?You can check the image

you can check with screen shot

Upvotes: 1

Views: 90

Answers (2)

Arnav Joshi
Arnav Joshi

Reputation: 96

If you are using oracle db please use below query to get desired records:

SELECT 
    COUNT(*) OVER () total_records,tr.*, t.* 
FROM tinfo t 
left join tutorregistration tr on tr. tid=t.tsid 
where pincode LIKE '%andhra%';

Upvotes: 0

Joswin K J
Joswin K J

Reputation: 710

You can use a sub-query.

SELECT *
FROM (
    SELECT tr.*, t.* 
    FROM tinfo t 
    JOIN tutorregistration tr on tr.tid = t.tsid 
    WHERE pincode LIKE '%andhra%'
) a
JOIN (
    SELECT tr.tid, count(pincode) as req_count
    FROM tinfo t 
    JOIN tutorregistration tr ON tr.tid = t.tsid 
    WHERE pincode LIKE '%andhra%'
) b ON a.tid = b.tid;

Upvotes: 1

Related Questions