Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Multiple WHERE clauses in INSERT INTO statement

    I am a relative newbie at SQL programming and am having to design a database. I need to input data into a table where I select data from multiple columns of another table, SUMing some of it but using separate WHERE clauses. I wish to do something like the following code
    INSERT INTO [Table2](r1,r2,r3) SELECT [d1], SUM ([d2]) WHERE [Name]=""a"", SUM ([d2]) WHERE [Name]=""b"" FROM [Table1] GROUP BY [d3];
    This does not work as the first WHERE condition on [d2] exists without a FROM (but adding this leads to the program only seeing 2 input data and 3 columns to input to).
    Does anyone know a way around this problem without complex coding. I hope this is a fairly simple problem but cannot find a specific example of it, so am working by trial and error.
    Thanks, Michael

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    your problem is in understendin how GROUP BY & WHERE works
    you can't have in one select 2 agregate functions (for example SUM) with different WHERE conditions, you need to rearange your select to have separate sub Selects with diferent WHERE conditions for each SUM , then link this subselects with left joins and put whole select in INPUT statement
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    I have tried to code what you suggest but I don't think I totally understand what you mean. Could you please post an example of the actual SQL code required.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try this.

    INSERT INTO [Table2](r1,r2,r3)
    VALUES (SELECT [d1], SUM ([d2]) WHERE [Name]=""a""), (SELECT SUM ([d2]) WHERE [Name]=""b"" FROM [Table1] GROUP BY [d3]);

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    michael, your GROUP BY was wrong, i'm assuming you want to group by d1

    PHP Code:
    insert 
      into Table2
         
    r1
         
    r2
         
    r3
         

    select d1
         
    sum(iif(Name='a',d2,0))
         , 
    sum(iif(Name='b',d2,0))
      
    from Table1 
    group 
        by d1 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Posts
    3
    Thanks, that worked exactly as I wanted. It was just my lack of knowledge of the coding that was causing the problems.
    Michael

Posting Permissions

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