| Author |
 Topic  |
|
|
RCC
Starting Member
2 Posts |
 Posted - 05/21/2012 : 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_info
part1 | 123
part2 | 321
part1 | 123
part3 | 1.513
Then the second table should look like :
Part_Name part_amount
part 1 | 2
part 2 | 1
part 3 | 1
It would be nice, if somebody could help me :)
RCC |
Edited by - RCC on 05/21/2012 10:49:38
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 05/21/2012 : 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_count
INSERT INTO Table2(Part_Name, part_amount)
SELECT Part_Name, COUNT(*) FROM Table1 GROUP BY part_Name |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 05/21/2012 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
RCC
Starting Member
2 Posts |
Posted - 05/22/2012 : 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.
|
Edited by - RCC on 05/22/2012 03:36:48 |
|
|
| |
Topic |
|
Compare two tables
