Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder xconspirisist's Avatar
    Join Date
    Jun 2006
    Location
    Great Britain.
    Posts
    138
    Thanks
    1
    Thanked 6 Times in 6 Posts

    Question Calculating fields by date

    This is rather a strange problem, one that might be hard to describe but I will do my best.

    I need to calculate fields for various dates and have them returned as a single result set. The desired result set would be something like listing1 (code section)

    That result set needs to come from the listing2 (some dummy data included);

    Each "month" field is calculated according to the formula in listing3.

    So far I have cobbled some SQL together, as seen in listing4.

    Currently the query executes, yet it returns zero results. I feel it's close if not rather nasty, but I'm missing something.

    I believe the issue is caused by the implicit joins or WHERE criteria.

    Can anyone help me fix the query?

    Code:
    listing1:
    ----------------------------------------------------------------------------
    | id   | name         | month1     | month2      | month3     | month4     |
    ----------------------------------------------------------------------------
    | 01   | Alice        | 2          | 3           | 1          | 7          |
    | 02   | Bob          | 1          | 2           | 5          | 0          |
    | 03   | Charles      | 6          | 3           | 8          | 9          |
    ----------------------------------------------------------------------------
    Code:
    listing2:
    +-----+----------+------+------------+
    | id  | name     | snum | fdate      |
    +-----+----------+------+------------+
    |  86 | Charles  |   84 | 2009-09-30 |
    | 265 | Bob      |   47 | 2009-09-29 |
    | 160 | Alice    |  149 | 2009-09-14 |
    |  80 | Charles  |  192 | 2009-09-24 |
    | 267 | Bob      |  139 | 2009-10-01 |
    | 152 | Alice    |  176 | 2009-09-06 |
    | 141 | Charles  |  163 | 2009-11-25 |
    | 316 | Bob      |   51 | 2009-11-20 |
    | 133 | Charles  |   83 | 2009-11-17 |
    | 235 | Alice    |    1 | 2009-11-29 |
    +-----+----------+------+------------+
    Code:
    listing3:
    $rowCount  =  The count of rows for a single name in a date period, where snum is under 100. 
    $rowCount / 30 * 100
    Code:
    listing4:
    SELECT   
            ful.name,
            (count(month1.snum) / 30 * 100) as month1calc,
            (count(month2.snum) / 30 * 100) as month2calc
    FROM 
            mytable as ful,
            (SELECT id, snum FROM mytable WHERE Month(fdate) = 11 ) as month1,
            (SELECT id, snum FROM mytable WHERE Month(fdate) = 10 ) as month2
    WHERE 
            (ful.snum < 100) 
            AND ful.id = month1.id
            AND ful.id = month2.id
    GROUP BY 
            ful.name
    ORDER BY 
            ful.name
    ;
    Many thanks in advance for your help, this issue has kept me amused all day
    Last edited by xconspirisist; 03-01-2009 at 09:58 PM.
    If I have been helpful, use the "thank" button - It makes me happy!

    xconspirisist.co.uk - homepage of my online alias
    technowax.net - a community for people interested in all forms of modern technology.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts

    Shoud be able to do that more easily...

    *IF* I understand the problem, should be able to do this:
    Code:
    SELECT name,
           ( SUM( IF(MONTH(fdate)=11,1,0) ) * 3.33 ) as month11,
           ( SUM( IF(MONTH(fdate)=10,1,0) ) * 3.33 ) as month10
    FROM mytable
    WHERE snum < 100 AND MONTH(fdate) IN (11,10)
    GROUP BY name
    ORDER BY name;
    No? The code in red isn't strictly needed, but it might improve performance.

    Might point out that if you have data in the table from multiple years, this will get the overall values for *ALL* years.

    Possibly you want to add
    Code:
    AND YEAR(fdate) = 2008
    or similar to the WHERE clause?

  • #3
    Regular Coder xconspirisist's Avatar
    Join Date
    Jun 2006
    Location
    Great Britain.
    Posts
    138
    Thanks
    1
    Thanked 6 Times in 6 Posts
    That is superb, thank you so very much.

    You answered my question, yet the query is actually intended for a MS Access database, which does not support the IF function Is there an alternative method you may be aware of, using pure SQL?

    edit: MS Access is baffeling, the "if" function is actually called "iif". Joy.
    Last edited by xconspirisist; 03-01-2009 at 10:37 PM.
    If I have been helpful, use the "thank" button - It makes me happy!

    xconspirisist.co.uk - homepage of my online alias
    technowax.net - a community for people interested in all forms of modern technology.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    As you noted, Access and MySQL are *IDENTICAL* in usage of IF...excepting only that Access spells it "IIF".

    This can also be made to work in MySQL or SQL Server using CASE WHEN in place of IF/IIF.
    Code:
    SELECT name,
           ( SUM( CASE WHEN MONTH(fdate)=11 THEN 1 ELSE 0 END ) * 3.33 ) as month11,
           ( SUM( CASE WHEN MONTH(fdate)=10 THEN 1 ELSE 0 END ) * 3.33 ) as month10
    FROM mytable
    WHERE snum < 100 AND MONTH(fdate) IN (11,10)
    GROUP BY name
    ORDER BY name;
    Luckily, all 3 DBs support the MONTH( ) function.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by xconspirisist View Post
    You answered my question, yet the query is actually intended for a MS Access database, which does not support the IF function
    Then why post your question in a mysql forum? they are completely different database applications.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •