Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Poland
    Posts
    18

    Unanswered: im beginner with sql.please help

    hi
    i work with sql server and created a transitory querry , result of which is the set of payable accounts order by Customer i stored in #tableindex1 and that looks like this:

    customer invoice date of payment(A) value difference (03.10 -"A") AS B
    --------- ------- -------------------- ----- ------------------------------
    201100 174 2003-09-19 600 13 days
    201100 230 2003-09-24 500 9 days
    201100 270 2003-09-26 300 7 days
    301000 100 2003-09-26 200 7 days


    Now i want to create a final querry that stores the data in the following way.
    IF #tableindex.B < 0
    THEN SUM invoices' value is stored in the column " C"
    IF #tableindex.B > 0 AND < 8
    THEN SUM invoices' value is stored in the column " D"
    IF #tableindex.B> 8 AND < 14
    THEN SUM invoices' value is stored in the column " E"

    Finally result should look like this:

    Customer sum of CDE C D E
    ---------- ------------- --- --- -----
    201100 1400 - 300 1100
    301000 200 - 200

    Thanks in advance for every help

  2. #2
    Join Date
    Oct 2003
    Posts
    16

    Good One

    that value inside of the SUM command is evaluated for every record returned, and then SUMed up afterwards. So you can put IF statements inside the SUM function to include or not include certain values.

    Like this

    insert into table values
    ( select customer, sum(invoice), sum(if(tableindex.B<0,invoice,0)),
    sum(if((tableindex.B >0) and (tableindex.B < 8),invoice,0)),
    sum(if((tableindex.B > 8) and (tableindex.B<14),invoice,0)))

    -Chris
    Make sure to visit my BiteSize SQL Tutorial

  3. #3
    Join Date
    Oct 2003
    Location
    Poland
    Posts
    18

    Re: Good One

    Originally posted by christodd
    that value inside of the SUM command is evaluated for every record returned, and then SUMed up afterwards. So you can put IF statements inside the SUM function to include or not include certain values.

    Like this

    insert into table values
    ( select customer, sum(invoice), sum(if(tableindex.B<0,invoice,0)),
    sum(if((tableindex.B >0) and (tableindex.B < 8),invoice,0)),
    sum(if((tableindex.B > 8) and (tableindex.B<14),invoice,0)))

    -Chris
    Make sure to visit my BiteSize SQL Tutorial
    Hello Chris ,Your solution would be really great for my problem, but whenever i want to run this querry i receive a message like this:
    Server: Msg 156, Level 15, State 1, Procedure nalezn2, Line 30
    Incorrect syntax near the keyword 'if'.
    Server: Msg 170, Level 15, State 1, Procedure nalezn2, Line 30
    Line 30: Incorrect syntax near ','.
    i remeind you that i work with sql server200 querry analyzer.maybe syntax:sum(if.....) doesn't work at this environment.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Try using CASE

    insert into table values
    (
    select customer,
    sum(invoice),
    sum(case when tableindex.B<0 then invoice else 0 end),
    sum(case when tableindex.B >0 and tableindex.B < 8 then invoice else 0 end),
    sum(case when tableindex.B > 8 and tableindex.B<14 then invoice else 0 end)

    FROM...

    )

  5. #5
    Join Date
    Oct 2003
    Location
    Poland
    Posts
    18
    Originally posted by cvandemaele
    Try using CASE

    insert into table values
    (
    select customer,
    sum(invoice),
    sum(case when tableindex.B<0 then invoice else 0 end),
    sum(case when tableindex.B >0 and tableindex.B < 8 then invoice else 0 end),
    sum(case when tableindex.B > 8 and tableindex.B<14 then invoice else 0 end)

    FROM...

    )
    Hello Guys
    Im really greatfull for your help.It really works with "case" but...
    while using querry as above i received the following messageServer: Msg 156, Level 15, State 1, Procedure nalezn2, Line 37
    Incorrect syntax near the keyword 'select'.
    Server: Msg 170, Level 15, State 1, Procedure nalezn2, Line 42
    Line 42: Incorrect syntax near ')'.
    I got rid: the word "values" in the "insert into" syntax,parenthesises in front of the "select" and at the end of querry.
    After that i got a message:
    Server: Msg 8118, Level 16, State 1, Procedure nalezn2, Line 36
    Column '#Tableindex4.costumer' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
    So I added GROUP BY 'konto' to my querry that finally looks like that:

    insert into #table
    select customer,
    sum(invoice),
    sum(case when #tableindex.B<0 then invoice else 0 end),
    sum(case when #tableindex.B >0 and tableindex.B < 8 then invoice else 0 end),
    sum(case when #tableindex.B > 8 and tableindex.B<14 then invoice else 0 end)
    FROM #tableindex GROUP BY Konto

    This time my querry runs. i checked some top rows.It seems to be really good.But only by curiosity i'd like to know why it didn't want to go with the syntax that you proposed.I mean the syntax with "Insert into table VALUES.... Isnt't it the same??
    Could be the reason the table tableindex is temporary and a table i insert into is also temporary??

  6. #6
    Join Date
    Oct 2003
    Location
    Poland
    Posts
    18

    Unhappy

    Originally posted by aseban
    Hello Guys
    Im really greatfull for your help.It really works with "case" but...
    while using querry as above i received the following messageServer: Msg 156, Level 15, State 1, Procedure nalezn2, Line 37
    Incorrect syntax near the keyword 'select'.
    Server: Msg 170, Level 15, State 1, Procedure nalezn2, Line 42
    Line 42: Incorrect syntax near ')'.
    I got rid: the word "values" in the "insert into" syntax,parenthesises in front of the "select" and at the end of querry.
    After that i got a message:
    Server: Msg 8118, Level 16, State 1, Procedure nalezn2, Line 36
    Column '#Tableindex4.costumer' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
    So I added GROUP BY 'konto' to my querry that finally looks like that:

    insert into #table
    select customer,
    sum(invoice),
    sum(case when #tableindex.B<0 then invoice else 0 end),
    sum(case when #tableindex.B >0 and tableindex.B < 8 then invoice else 0 end),
    sum(case when #tableindex.B > 8 and tableindex.B<14 then invoice else 0 end)
    FROM #tableindex GROUP BY Konto

    This time my querry runs. i checked some top rows.It seems to be really good.But only by curiosity i'd like to know why it didn't want to go with the syntax that you proposed.I mean the syntax with "Insert into table VALUES.... Isnt't it the same??
    Could be the reason the table tableindex is temporary and a table i insert into is also temporary??
    i was wrong.It doesnt work good

  7. #7
    Join Date
    Oct 2003
    Posts
    16

    SUM is an aggregate function

    Whenever you are using an aggregate function like SUM, COUNT, or AVG you must group by the remaining columns that are not inside the SUM, COUNT, or AVG.

    I've been using mySQL too long, and it adds them automatically if you don't list them, so I didn't mention it.

  8. #8
    Join Date
    Oct 2003
    Location
    Poland
    Posts
    18

    Re: SUM is an aggregate function

    Originally posted by christodd
    Whenever you are using an aggregate function like SUM, COUNT, or AVG you must group by the remaining columns that are not inside the SUM, COUNT, or AVG.

    I've been using mySQL too long, and it adds them automatically if you don't list them, so I didn't mention it.
    Thank you guys.You really helped me to find the solution.Im really gratefull.

Posting Permissions

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