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 |
|
RCC
Starting Member
2 Posts |
 Posted - 2012-05-21 : 10:48:18
|
| Hi guys,i am new at SQL programming and need your help.I think the problem is not very hard to solve, but i do'nt know how to do it in SQL.There are two tables.In the first one are several different parts.At the moment the second table is empty. I want to count the amount of each part (in the first table) and then write the name and the amount of the part (from table one) in the second table. For example :Table 1 :Part_Name part_infopart1 | 123part2 | 321part1 | 123 part3 | 1.513Then the second table should look like :Part_Name part_amountpart 1 | 2part 2 | 1part 3 | 1It would be nice, if somebody could help me :) RCC |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-05-21 : 11:02:37
|
| Well, that's derived data, and will be stale as soon as you populate the table, and could be wrong when you rteferencece it in the future..and part_amount is a misnomer..should be part_countINSERT INTO Table2(Part_Name, part_amount)SELECT Part_Name, COUNT(*) FROM Table1 GROUP BY part_Name |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-21 : 16:17:08
|
| why do you want aggregates to be stored in physical table like that? You have to make sure you maintain this process in future whenever new part details are getting added to Table 1 to reflect changes tp aggregate else it would become out of sync.I would prefer to do this only at time of need by means of an on the fly query rather than putting it in a query as aggregation involved is not very complicated------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
RCC
Starting Member
2 Posts |
Posted - 2012-05-22 : 02:09:04
|
| Thanks for the help.I also will think about the db design,too.Edit : Is it possible to update the code ? Now there is a third table, which contain all parts. If there's a part in table one, that is missing in table threee, SQL should throw an Execption and do not write this "unknown" part in table 2.As well i think that the database design is okay at the moment,because i only have to derive some tables one time . Then i can "delete" the original and if i have something to change in the derived table, i can do it manually. |
|
|
|
|
|
|
|
|
Compare two tables