Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    61

    Unanswered: plz help with SUM syntax

    since i'm new to mysql i'm using MS access as a mirror.

    I basically created the EXACT same tables, relationships in MS access as in my mysql database.

    In MS access i created this query:

    SELECT Sum([MAIN]![DAY]+[MAIN]![NIGHT]) AS TOTALTIME, MODEL.MODEL
    FROM MODEL INNER JOIN MAIN ON MODEL.MODEL_ID = MAIN.MODEL_ID
    GROUP BY MODEL.MODEL, MAIN.PER_ID
    HAVING (((MAIN.PER_ID)="5"));

    I'm using php as the web interface for mysql database.

    I noticed that MS access sql code is different that MYSQL.

    What i'm having problem with is this line:
    Sum([MAIN]![DAY]+[MAIN]![NIGHT]) AS TOTALTIME

    I'm trying to add the SUM of DAY and the SUM of NIGHT.

    I tried:
    SELECT Sum(Sum(MAIN.DAY))+(Sum(MAIN.NIGHT)) AS TOTALTIME ...etc... but it didn't work

    The rest of the sql code works fine works fine...
    any ideas?
    thanks
    Noam

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: plz help with SUM syntax

    Originally posted by noamkrief
    I noticed that MS access sql code is different that MYSQL.
    understatement of the year

    well, so far, anyway


    some tips:

    don't use a reserved word like `day` as a column name

    don't use the same name for both a table and a column

    use WHERE instead of HAVING for filter conditions

    make sure the GROUP BY clause contains only the non-aggregate columns used in the SELECT list


    try this:
    Code:
    select sum(`day`+night) as totaltime
         , model.model
      from model 
    inner
      join main 
        on model.model_id = main.model_id
     where main.per_id = 5
    group
        by model.model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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