How to count column in inner join query

Question

Hi how can I create a query for this in inner join to count also on how many id in the stdtbl_detail ?

for example in stdtbl the id is "372", so I want to count how many 372 in stdtbl_detail

SELECT h.*, d.* from
  stdtbl h inner join stdtbl_detail d
  on h.id = d.std_dtl_id

  where
  h.loginid = '1' AND  h.stdid='013777'

so the output would be like this

id               stdid              loginid          std_dtl_id              countexist   


372              013777                1                  372                    4       

372              013777                1                  372                    4

372              013777                1                 372                     4

372              013777                 1                 372                    4

373              013777                1                  373                    4  

373              013777                 1                  373                    4

373              013777                 1                   373                   4

373              013777                1                     373                   4

Thank you in advance

here is the Demo

Answer 1

select h.*
     , d.*
     , count(*)
from stdtbl h
inner join stdtbl_detail d on h.id = d.std_dtl_id
where h.loginid = '1'
  and  h.stdid ='013777'
group by id

That will give you an answer. Do you need each id to show up every time, or just once per id?

Updated SQLFiddle

EDIT

This one will give you exactly what you asked for, just in case you also need that

select h.*
     , d.*
from stdtbl h  
inner join (
    select std_dtl_id
         , count(*) total
    from stdtbl_detail
    group by std_dtl_id
) d on h.id = d.std_dtl_id
inner join stdtbl_detail dt on h.id = dt.std_dtl_id
where h.loginid = '1'
  and  h.stdid = '013777'

SQLFiddle

Answer 2

Try this query

SELECT h.*, d.* , count(*) 
FROM stdtbl h 
INNER JOIN stdtbl_detail d ON h.id = d.std_dtl_id
WHERE h.loginid = '1' AND  h.stdid='013777'
GROUP BY h.id