Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rohit04413
Yak Posting Veteran
72 Posts |
 Posted - 2010-04-25 : 08:16:11
|
| i want to return 3 values from a select command so that i can insert them into 3 different columns of a table (using one insert command). the following statement works fine:insert into FTY(partcode,Totaltested) SELECT PartCode,count(SerialNumber) FROM atelog where (date>='01/01/2010' and date<='01/09/2010') GROUP BY PartCodei want to insert values into 3rd columns of FTY table using the same above statement. i must get those values from a select statement.i am trying the following statement but in vain:insert into FTY(partcode,Totaltested,passed) SELECT PartCode,count(SerialNumber) FROM atelog where (date>='01/01/2010' and date<='01/09/2010') GROUP BY PartCode,count(SerialNumber) FROM atelog where (date>='01/01/2010' and date<='01/09/2010' and Result='pass') GROUP BY PartCodetable atelog has Columns(SerialNumber int, PartCode varchar, Result varchar, Date smalldatetime)table FTY has Columns(partcode varchar, totaltested int, passed int)please help |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-25 : 09:24:53
|
Try this:INSERT INTO FTY(partcode,Totaltested,passed) SELECT PartCode, COUNT(SerialNumber), SUM(CASE WHEN Result='pass' THEN 1 ELSE 0 END) FROM atelog WHERE (date>='01/01/2010' and date<='01/09/2010') GROUP BY PartCode ------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
rohit04413
Yak Posting Veteran
72 Posts |
Posted - 2010-04-25 : 09:28:27
|
quote:
Originally posted by DBA in the making
Try this:INSERT INTO FTY(partcode,Totaltested,passed) SELECT PartCode, COUNT(SerialNumber), SUM(CASE WHEN Result='pass' THEN 1 ELSE 0 END) FROM atelog WHERE (date>='01/01/2010' and date<='01/09/2010') GROUP BY PartCode ------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee.
Thank You So Much....Will you please explain how that works? sum and case ? |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-25 : 09:41:05
|
quote:
Originally posted by rohit04413Thank You So Much....Will you please explain how that works? sum and case ?
Sure. The SUM() is similar to the COUNT() you used, but instead of returning the count of items, it returns the sum. So it you had a table (a) with column (b), with 4 rows, 1, 2, 3, 4, then 'SELECT SUM(b) FROM a' would return 10 (1 + 2 + 3 + 4). It adds them all up. The CASE is a little like an inline IF/THEN/ELSE. It will return either a 1 (in the case of a pass) or ELSE it will return a 0 (for anything else). It does this for every record matching the WHERE clause. Once it returns these 1's and 0's to the SUM() function, the SUM() function is used to add up the 1's, essentially counting the records that have passed. To give you an idea of what the CASE does, run this query:SELECT PartCode, Result, CASE WHEN Result='pass' THEN 1 ELSE 0 END AS CaseResultFROM atelog WHERE (date>='01/01/2010' and date<='01/09/2010') Notice that every time Result='pass', CaseResult=1, otherwise it = 0.If you want to know more about CASE, look it up in Books Online. It's a really handy construct to understand.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
rohit04413
Yak Posting Veteran
72 Posts |
Posted - 2010-04-25 : 09:52:40
|
| Thanks dear.....you have saved a lot of my time.can you please suggest a good book for learning sql fast, i have some good knowledge of C/C++ only. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-25 : 09:58:24
|
quote:
Originally posted by rohit04413
Thanks dear.....you have saved a lot of my time.can you please suggest a good book for learning sql fast, i have some good knowledge of C/C++ only.
I'm not really sure. I didn't learn much SQL from books. O'Reilly usually produce the goods when it comes to IT books. Maybe have a look at some of theirs.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
rohit04413
Yak Posting Veteran
72 Posts |
Posted - 2010-04-25 : 10:14:53
|
| i want more of your little help...now i want FTY table to have 4th column which stores the value calculated by (passed/totaltested*100) for every record in table fty.please reply soon |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-25 : 10:31:19
|
quote:
Originally posted by rohit04413
i want more of your little help...now i want FTY table to have 4th column which stores the value calculated by (passed/totaltested*100) for every record in table fty.please reply soon
I'll give you some code to insert that, but you might want to re-think that idea. Those values can be easily calculated by the existing data in the table, so there's really no point in storing them.INSERT INTO FTY(partcode,Totaltested,passed,fourth_column_name)SELECT PartCode, Tested, Pass, (100 * Pass) / TestedFROM ( SELECT PartCode, COUNT(SerialNumber) AS Tested, SUM(CASE WHEN Result='pass' THEN 1 ELSE 0 END) AS Pass FROM atelog WHERE (date>='01/01/2010' and date<='01/09/2010') GROUP BY PartCode) z This will produce an integer result. If you want a floating point result, then use a CAST, like this:(100 * CAST(Pass AS DECIMAL(8, 2))) / CAST(Tested AS DECIMAL(8, 2)) ------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
rohit04413
Yak Posting Veteran
72 Posts |
Posted - 2010-04-25 : 10:44:18
|
quote:
Originally posted by DBA in the making
quote:
Originally posted by rohit04413
i want more of your little help...now i want FTY table to have 4th column which stores the value calculated by (passed/totaltested*100) for every record in table fty.please reply soon
I'll give you some code to insert that, but you might want to re-think that idea. Those values can be easily calculated by the existing data in the table, so there's really no point in storing them.INSERT INTO FTY(partcode,Totaltested,passed,fourth_column_name)SELECT PartCode, Tested, Pass, (100 * Pass) / TestedFROM SELECT PartCode, COUNT(SerialNumber) AS Tested, SUM(CASE WHEN Result='pass' THEN 1 ELSE 0 END) AS Pass FROM atelog WHERE (date>='01/01/2010' and date<='01/09/2010') GROUP BY PartCode) z This will produce an integer result. If you want a floating point result, then use a CAST, like this:(100 * CAST(Pass AS DECIMAL(8, 2))) / CAST(Tested AS DECIMAL(8, 2)) ------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee.
i was also thinking that it can be calculated by existing data but how can i do that? i tried to put a formula in computed column properties of fourth column in fty table definition, but that is returning only 0 and 100, not the values inbetween. |
|
|
|
rohit04413
Yak Posting Veteran
72 Posts |
Posted - 2010-04-25 : 10:54:10
|
quote:
Originally posted by DBA in the making
quote:
Originally posted by rohit04413
i want more of your little help...now i want FTY table to have 4th column which stores the value calculated by (passed/totaltested*100) for every record in table fty.please reply soon
I'll give you some code to insert that, but you might want to re-think that idea. Those values can be easily calculated by the existing data in the table, so there's really no point in storing them.INSERT INTO FTY(partcode,Totaltested,passed,fourth_column_name)SELECT PartCode, Tested, Pass, (100 * Pass) / TestedFROM SELECT PartCode, COUNT(SerialNumber) AS Tested, SUM(CASE WHEN Result='pass' THEN 1 ELSE 0 END) AS Pass FROM atelog WHERE (date>='01/01/2010' and date<='01/09/2010') GROUP BY PartCode) z This will produce an integer result. If you want a floating point result, then use a CAST, like this:(100 * CAST(Pass AS DECIMAL(8, 2))) / CAST(Tested AS DECIMAL(8, 2)) ------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee.
this code is giving some error |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-25 : 11:09:59
|
quote:
Originally posted by rohit04413]i was also thinking that it can be calculated by existing data but how can i do that? i tried to put a formula in computed column properties of fourth column in fty table definition, but that is returning only 0 and 100, not the values inbetween.
Don't use "(passed/totaltested*100)", that will give you rounding errors. Basically, passed and totaltested are integers, so the result will be an integer. Because totaltested >= passed, passed/totaltested will always result in either 0 or 1. If you use "(100 * Pass) / totaltested", then pass is multiplied by 100 first, then devided by totaltested. The result will still be an integer, but it will be rounded to single whole units (eg, 0, 1, 2, 3), rather than 0, 100. quote:
Originally posted by rohit04413this code is giving some error
Oops, I lift a '(' out. Place a ( after the first FROM (at the end of the third line). See my edited post.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
rohit04413
Yak Posting Veteran
72 Posts |
Posted - 2010-04-25 : 11:22:01
|
quote:
Originally posted by DBA in the making
quote:
Originally posted by rohit04413]i was also thinking that it can be calculated by existing data but how can i do that? i tried to put a formula in computed column properties of fourth column in fty table definition, but that is returning only 0 and 100, not the values inbetween.
Don't use "(passed/totaltested*100)", that will give you rounding errors. Basically, passed and totaltested are integers, so the result will be an integer. Because totaltested >= passed, passed/totaltested will always result in either 0 or 1. If you use "(100 * Pass) / totaltested", then pass is multiplied by 100 first, then devided by totaltested. The result will still be an integer, but it will be rounded to single whole units (eg, 0, 1, 2, 3), rather than 0, 100. quote:
Originally posted by rohit04413this code is giving some error
Oops, I lift a '(' out. Place a ( after the first FROM (at the end of the third line). See my edited post.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee.
Thanks Sir,my problem has been solved by you.may i have your e mail address if i face any problems further |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-25 : 12:49:00
|
| If you have any further problems, just post them here. That's what the site's for. If I'm not around, someone else will be, probably with more knowledge than I have.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
rohit04413
Yak Posting Veteran
72 Posts |
Posted - 2010-04-25 : 13:07:18
|
quote:
Originally posted by DBA in the making
If you have any further problems, just post them here. That's what the site's for. If I'm not around, someone else will be, probably with more knowledge than I have.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee.
its ok thanks again |
|
|
|
|
|
|
|
|
returning 3 values from select statement