satish kilari satish kilari - 13 days ago 8
SQL Question

Join two tables get count without using any where condition

I am trying to join two tables, prcshd (head table) and prcsdt (detail table), and need to get count of prod_prcshd_id in prcsdt (detail table) without using where (if required can use sub-query). Not sure ...

Tried like

select distinct count(b.prod_prcshd_id), b.prod_prcshd_id
from tra_pharmacy_prod_prcshd a join
tra_pharmacy_prod_prcsdt b
on b.prod_prcshd_id = a.id
group by b.dt_id


My tables:

prcshd
(head table)

id(pk) | medi_name_id | med_prep_id
1 83 1
2 83 2


prcsdt
(detail table)

dt_id(pk) | prod_prcshd_id(fk) | type_id | prod_name_id |medi_prep_id
1 1 4 83 1
2 1 5 83 1
3 1 6 83 1
4 2 4 83 2


still no luck.

id | prod_prcshd_id | medi_name_id
1 3 83
2 1 83

Answer

I'm not sure if this will fix your problem. But, you almost never need select distinct with group by. I suspect you want one of these this:

select b.prod_prcshd_id, count(b.prod_prcshd_id)
from tra_pharmacy_prod_prcshd a join
     tra_pharmacy_prod_prcsdt b
     on b.prod_prcshd_id = a.id
group by b.prod_prcshd_id;

Under most circumstances, you don't even need the join:

select ppp.prod_prcshd_id, count(*)
from tra_pharmacy_prod_prcsdt ppp
group by ppp.prod_prcshd_id;
Comments