join in query

[morfasie]

If I have a table like this :
name | money | date
--------------------
group 1 | 10 | january
group 1 | 10 | february
group 1 | 10 | march

another table with memebrs in it.

I want to sum the money in the first table, and do a count on the second table, to see how many members the group has.

my join multiplies the money sum with the count etc.

select sum(a.money_received),count(*)
from schedule_premiums a
left outer join productioncalc b
on a.`group name` = b.`group name`
where a.`group name` = 'africa granite'

what is wrong here??

 

[r937]

what is wrong is that you're joining the tables

select sum(a.money_received)
     , ( select count(*) 
           from productioncalc 
          where `group name` 
              = a.`group name`) as rows
  from schedule_premiums a
 where a.`group name` = 'africa granite'

r937.com | rudy.ca