Re: Problem with COUNT in the statement JOIN

[irek kordirko]

Chevy Mark Sunderland Wrote:
-------------------------------------------------------
> Thanks a lot, I understand your explanation.
>
> If I need this output how to sql query ?
>
> x = (total record of myfirsttablemysql for
> year=2011) / (hour_1 + hour_2 of
> mysecondtablemysql for all month for year=2011)
>
> Can you help me?

Do it according to the formula:

1. (total record of myfirsttablemysql for year=2011)

  SELECT  
  COUNT(*) `total`
  FROM myfirsttablemysql
  WHERE (YEAR(`myDatesYYYYMMDD`)='2011') ;

+-------+
| total |
+-------+
|    54 |
+-------+

2. (hour_1 + hour_2 of mysecondtablemysql for all month for year=2011)

  SELECT SUM( hour_1 + hour_2)
  FROM mysecondtablemysql
  WHERE years = 2011

+-----------------------+
| SUM( hour_1 + hour_2) |
+-----------------------+
|                939935 |
+-----------------------+

3. Divide #1 by #2

 SELECT ( 
      SELECT cast( COUNT(*) as decimal(10,8))
      FROM myfirsttablemysql
      WHERE (YEAR(`myDatesYYYYMMDD`)='2011')
   )  
   /
   ( 
        SELECT cast( SUM( hour_1 + hour_2) as decimal(10,0) )
        FROM mysecondtablemysql
        WHERE years = 2011
   ) As result
;

+----------------+
| result         |
+----------------+
| 0.000057450781 |
+----------------+