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

Question

SELECR Sire, COUNT (DISTINCT Id) as AB FROM [1]
GROUP BY sire

I would add column AB to its table [1]!!!!!!!

Answer 1

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,

Answer 2

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

Answer 3

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

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

Answer 5

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

Answer 6

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,

Answer 7

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,

Answer 8

Hi @ipang, How you doin'? could you help me in this query please???

Answer 9

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!

Answer 10

many thanks for taking your time and your help. 💐💐💐 @ipang

Answer 11

Hi @Garvin,
Thanks a lot for your prompt answer! 💐💐💐

Answer 12

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! 😑

Answer 13

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

Answer 14

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.