+ 1

Hi Guys, how can I add a results column to existing table?

SELECR Sire, COUNT (DISTINCT Id) as AB FROM [1] GROUP BY sire I would add column AB to its table [1]!!!!!!!

19th Jul 2017, 10:32 PM
Reza Reisi Vanani
Reza Reisi Vanani - avatar
14 odpowiedzi
+ 4
Hi @Reza, I was thinking to start with just simply adding that column, and work something out afterwards, but I appreciate your decision anyhow. Sorry about the delays, internet connection here wasn't so reliable. Best of luck @Reza,
23rd Jul 2017, 12:41 AM
Ipang
+ 4
Hello again @Reza, all Ok, how bout you? Your query tells me you want to count number of records grouped by "Sire" field? did the query work? so what is this "Id" field? Anyway would you mind posting the structure of table [1]?. I need to understand the structure before I can offer any suggestions. I'm listening...
20th Jul 2017, 2:06 AM
Ipang
+ 4
@Reza, that sounds pretty serious, honestly, I've been feeling uneasy about this because I'm not sure that I get your intention correctly nor am I giving you the right suggestion. I suppose you are not allowed to share the structure of this table?. Have you tried adding the new column manually? I assume you know how to do that. Is adding new column enough to solve the problem? if it's not enough, please explain. If you want to add a calculated column that can update automatically, I need to know when the calculation should occur, on new record, record update, or delete, this is related to triggers, they work on events. I also need to know which amongst the columns in the table that is used to formulate the calculated column data. For example, let's say we have a student table with an auto-calculated score/mark column, how do we get the score? diligence + activities + homework + attitude are the columns that formulate the score. Which columns in your table formulate the calculated column? I'm still listening..
22nd Jul 2017, 4:14 AM
Ipang
+ 4
Hi @Reza, Here's the command to add a new column into an existing table. Execute this command and tell me if it works, this will create a new permanent column in the table. Note that you need to change <table_name> with your table name, and <new_column_name> with a column name you want. If your table name contains space, please wrap the table name in double quotes. e.g. if table name is cow data make it "cow data". ALTER TABLE <table_name> ADD <new_column_name> int NOT NULL DEFAULT 0; Talk to me again after that, Ok, I'm still here..
22nd Jul 2017, 2:20 PM
Ipang
+ 3
You can try using a join command. (SELECT * FROM [1]) as table1 LEFT JOIN (SELECT Sire, COUNT(DISTINCT Id) as AB FROM [1] GROUP BY Sire) as table2 ON table1.Sire=table2.Sire
19th Jul 2017, 10:46 PM
Ghauth Christians
Ghauth Christians - avatar
+ 3
My idea goes like: father(table) father_id int primary not null father_name varchar(50) not null children int null daughters(table) father_id int * - linked to father.father_id daughter_name varchar(50) Create triggers to fire on insert, update, and delete for the daughters table, those triggers should update the (number of) children field in father table. This way you wouldn't need to worry having to manually update number of children of the father's table. Hth,
20th Jul 2017, 11:06 AM
Ipang
+ 3
You're welcome @Reza, Actually you still have the option to add a new column in the table to store the calculation result, a permanent field. But it will create lots of redundancy of data. That's why I recommend you to split the table in two. I might have other suggestion if you would explain what & why you're doing this, and what is your actual target. And it would surely help a lot if you show the table structure, it will speak on its behalf. Hth,
20th Jul 2017, 11:23 PM
Ipang
+ 1
Hi @ipang, How you doin'? could you help me in this query please???
19th Jul 2017, 10:34 PM
Reza Reisi Vanani
Reza Reisi Vanani - avatar
+ 1
Hello @Ipang, thanks a lot. yes, the query worked well. Id is number of daughters. there is just one table and I would add the AB column (result column from the query) to its table, if it's possible? (actually I want to add a SELECT query result column to its table, I mean how can I change a temporary column to permanent) thank you!
20th Jul 2017, 5:09 AM
Reza Reisi Vanani
Reza Reisi Vanani - avatar
+ 1
many thanks for taking your time and your help. 💐💐💐 @ipang
20th Jul 2017, 10:18 PM
Reza Reisi Vanani
Reza Reisi Vanani - avatar
0
Hi @Garvin, Thanks a lot for your prompt answer! 💐💐💐
20th Jul 2017, 5:11 AM
Reza Reisi Vanani
Reza Reisi Vanani - avatar
0
Thanks for taking you time. @Ipang I'm working with big data, like 400,000 thousands rows and 15 columns. I intend to work on it without split the table into any table, so there is just one table and I want to add the results column (make them permanently) in that table I'm working with, without creating any new table or second table. I hope you get the piont with my poor language! 😑
21st Jul 2017, 11:45 PM
Reza Reisi Vanani
Reza Reisi Vanani - avatar
0
@Ipang, No there's not any secret in the table that I won't to share with you, see I have 15 variables columns about cows, like when were they born or sth like these characters. I would edit this data and exclude the wrong details. For example when I executed this description, SELECT Sire, COUNT (DISTINCT Id) as AB FROM [1] GROUP BY sire So I would add the AB column to [1] again, So there will be 16 columns if I could made AB as a permanent column and join it to the only table I'm working with, because I would edit AB column again, but until it's not permanently I couldn't do any edition on AB.
22nd Jul 2017, 1:42 PM
Reza Reisi Vanani
Reza Reisi Vanani - avatar
0
Hello @Ipang, Thank you so much again for you time! The command worked well, but.... I am considering I couldn't explain you what I want to do exactly. I am pretty give up on that! I really appreciate you for everything and your prompt reply.
22nd Jul 2017, 9:53 PM
Reza Reisi Vanani
Reza Reisi Vanani - avatar