CODEWITHSUNDEEP
sqlpostgresql
Abhinaw Anand
Abhinaw Anand

Reputation: 41

how to find count of row in different table

I want to get data from two different table one table contains total student another table contains specific student information how i can get the count of student i want to display name , code, totalstudent and no.of ngo student 

select 
       a.name as name, a.school_code as CODE, 
       a.num_of_student as totalstudent,
       b.COUNT (ngo_student_name) as total_student 
from 
      ngo_student as a 
      INNER JOIN student_details as b on a.name=b.ngo_student_name 
GROUP BY
      b.ngo_student_name

this query showing error please guide me thanks

Upvotes: 2

Views: 59

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Try below- your count(b.ngo_student_name) instead of b.count(ngo_student_name) and also other columns in selecttion list should be in group by clause

select 
       a.name as name, a.school_code as CODE, 
       count(a.num_of_student) as totalstudent,
       COUNT(b.ngo_student_name) as total_student 
from 
      ngo_student as a 
      INNER JOIN student_details as b on a.name=b.ngo_student_name 
GROUP BY
      a.name,a.school_code

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try the below SQL statement

 SELECT A.NAME AS NAME, A.SCHOOL_CODE AS CODE, '' AS TOTALSTUDENT , '' AS [NO.OF NGO STUDENT] FROM NGO_STUDENT
UNION ALL
SELECT TOP 1 '' AS NAME, '' AS CODE, (SELECT COUNT(DISTINCT(NGO_STUDENT_NAME)) FROM NGO_STUDENT) AS TOTALSTUDENT , 
(SELECT COUNT(DISTINCT(NGO_STUDENT_NAME)) FROM STUDENT_DETAILS) AS [NO.OF NGO STUDENT]

Upvotes: 0

Related Questions