dimanche 29 novembre 2015

SQL JOINT and COUNT

I have 2 tables:

  • r1 : with primary key k1 ;
  • r2 : whih secondary key k1 (from r1 table) duplicate authorised

r2 can therefore have several line having k1 as secondary key on which a SUM, for instance could be perform.

My problem is that when I try

select
  r1.col_n,
  sum(r2.col_m),
  **count(*)as total**
from
  r1,r2
where
  r1.k1 = r2.k1

The COUNT(*) here will return the number of occurence or r2.k1 I understand this is what it is suppose to do and thats what the query means.

My problem is (and dont ask me why because lots of secondary keys are in r1... lol) I would like the COUNT() to return me the number of occurrence of r1.k1 in that joint query instead of r2.k1 ... I need the sum(r2.col_m) and the count() on r1...

Could you guyz help me here ?

Aucun commentaire:

Enregistrer un commentaire