MySQL Forums :: Newbie :: Problem with COUNT in the statement JOIN

Chevy Mark Sunderland Wrote:
-------------------------------------------------------
> thanks a lot.
>
> I need group by region, I try this statement but I
> have error:
>

Hmmm, that's a change request, we need to set a date and calculate man-hours and a pricing ;-)

SELECT a1.region, firstsum / secondsum
FROM ( 
      SELECT region,
             cast( COUNT(*) as decimal(15,8)) firstsum
      FROM myfirsttablemysql
      WHERE (YEAR(`myDatesYYYYMMDD`)='2011')
      GROUP BY region
   )  a1
JOIN 
   ( 
        SELECT region,
               cast( SUM( hour_1 + hour_2) as decimal(10,0) )  secondsum
        FROM mysecondtablemysql
        WHERE years = 2011
        GROUP BY region
) a2
ON a1.region = a2.region

;


+--------+----------------------+
| region | firstsum / secondsum |
+--------+----------------------+
| PMC4M  |       0.000026663823 |
| PMC4P  |       0.000045338109 |
| PMC6M  |       0.000058469274 |
| PMC6O  |       0.000061814885 |
| PMC6P  |       0.000038197097 |
| SOT1M  |       0.000033345560 |
| SOT1N  |       0.000106288751 |
| SOT1P  |       0.000049094212 |
| SOT1Q  |       0.000115764188 |
| SOT5M  |       0.000065400085 |
| SOT5N  |       0.000035166690 |
| SOT5P  |       0.000042101718 |
| SOT5Q  |       0.000067353674 |
| SOT5R  |       0.000035559349 |
| SOT6M  |       0.000032678671 |
| SOT6O  |       0.000121256214 |
+--------+----------------------+

Edited 3 time(s). Last edit at 02/28/2012 09:05AM by irek kordirko.