MySQL Consultant for Query Support

Discussion in 'OT Technology' started by Peyomp, Sep 15, 2006.

  1. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    MySQL AB charges $5000/year for the support package that includes Query support. We do alot of statistics generation in SQL, and so I need support on our queries. I need a MySQL expert for this purpose. Where can I find one?

    For example, just so not everyone raises their hand as an 'expert,' I have this query:

    PHP:
    SELECT a.vendor_id as vendorDATE_FORMAT(datetimeBegin"%c/%e") as dateCOALESCE(FORMAT(sum(b.grossSales),2),0) as totalGrossSales FROM games a JOIN gameEntries b WHERE a.configId=b.game_configId AND datetimeBegin >= 20060101000000 AND datetimeEnd <= 20060231000000  GROUP BY a.vendor_idDATE_FORMAT(datetimeBegin"%c/%e"ORDER BY a.vendor_idMONTH(datetimeBegin), DAY(datetimeBegin);
    and I need to modify it so that where there are no entries in the database for a given day, that it will return a 0 for that field, instead of nothing, since there are no entries for that day to group by.

    A non-trivial question. I can find the answer, but there are many such questions and I do not have time to answer them all as I have other tasks to complete.

    By the way, if you CAN answer it and more complicated questions, then YOU can be my MySQL DBA. Drop me a line to [email protected]
     
  2. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
  3. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    I think I'm gonna get a 6 month Platinum support contract, if they'll do that, for $2500.
     
  4. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    I think you can use IFNULL(value,return):

    Code:
    SELECT a.vendor_id as vendor, DATE_FORMAT(datetimeBegin, "%c/%e") as date, IFNULL(COALESCE(FORMAT(sum(b.grossSales),2),0),0) as totalGrossSales
    FROM games a
    JOIN gameEntries b
    WHERE a.configId=b.game_configId
    AND datetimeBegin >= 20060101000000
    AND datetimeEnd <= 20060231000000
    GROUP BY a.vendor_id, DATE_FORMAT(datetimeBegin, "%c/%e")
    ORDER BY a.vendor_id, MONTH(datetimeBegin), DAY(datetimeBegin);
     
  5. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    Nm, that'd assume that there was something to group by still.
     
  6. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Exactly. Which is why I'm stumped. MySQL said they would do a six month platinum support contract. Guess I'll do that, Monday.
     
  7. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    Fairly simple. Just outer join against a calendar table.
     
  8. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Ahhh... so I need to create a calendar table for hourly periods then. Hmmmm.

    Thanks.
     

Share This Page