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 helpthat 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 (http://www.bitesizeinc.net/index.php/sql.html)|||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 (http://www.bitesizeinc.net/index.php/sql.html)

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.|||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...

)|||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??|||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|||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.|||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.