# Making a new column and setting default values equal to the COUNT of a certain condition in SQL? [closed]

I have 2 tables. (table1, table2) Now, I would like to do something along the lines of:

ALTER TABLE table1 ADD counter INT DEFAULT (Select COUNT(table2.car_type) FROM table2 WHERE (table1.car_type = table2.car_type));


That's the closest syntax I can come up with, but just by looking at it I know it's wrong. Please tell me how to do it in ONE SQL statement (if possible).

The goal table (with counter implemented) is something like this: (separated columns by -)

table1: pid-car_type-counter:

1-Honda-2
2-Toyota-3
3-Suzuki-1
4-Ferrari-0
5-Porsche-1


table2: pid-car_type:

1-Honda
2-Toyota
3-Porsche
4-Honda
5-Suzuki
6-Toyota
7-Toyota

-

## closed as off-topic by Mat's Mug, palacsint, MrSmith42, Vogel612, MalachiApr 18 '14 at 12:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

• "Questions containing broken code or asking for advice about code not yet written are off-topic, as the code is not ready for review. Such questions may be suitable for Stack Overflow or Programmers. After the question has been edited to contain working code, we will consider reopening it." – Mat's Mug, palacsint, MrSmith42, Vogel612, Malachi
If this question can be reworded to fit the rules in the help center, please edit the question.

what flavor of SQL are we dealing with here? –  Malachi Apr 18 '14 at 2:12
I'm using SQL Plus to run my scripts. So, oracle I think. –  user40128 Apr 18 '14 at 2:18
I had a little doubt when I posted my answer, but with the comments it's getting clearer - you shouldn't be expecting this community to fix any issues in your code, that's Stack Overflow's job ;) ...we review working code (my answer just assumes your code works) and often come up with alternative ways or cosmetic changes, but CR isn't for changing what the code does.. actually posting non-working code is off-topic for this site, see our help center for all the details ;) –  Mat's Mug Apr 18 '14 at 3:04
Oh... I assumed they were both the same. Should I delete this question then? –  user40128 Apr 18 '14 at 3:06
I don't think you can, now that there are answers... mind if I cast a close vote? It'll enter the close queue and will eventually be deleted then. (see how is this site different from stack overflow? on meta - and feel free to stick around and come back with some working code to review, too!). –  Mat's Mug Apr 18 '14 at 3:12

That one-liner is hard to read!

ALTER TABLE table1
ADD counter INT DEFAULT (SELECT COUNT(table2.car_type)
FROM table2
WHERE (table1.car_type = table2.car_type));


Oracle is a bit foreign to me, but if I read this correctly you're adding am int column called counter to a table called table1, with a default constraint that you'd like to be somewhat dynamic (I never tried that, but my guts agree with your doubts).

I'd script that in two operations - one to drop/create the table column, another to drop/create the default constraint.

However I don't think a constraint is the right approach for this - it's not a default value you want in that table, it's an automatic value. When a new toyota is added in table2 you'll want that counter in table1 to remain accurate!

I think such a column isn't useful (if you need the count of toyota cars, why not count them when you need to and get an accurate figure every time?), but if you really really really need to maintain such a counter then I'd resort to a trigger (I think triggers should be avoided): when a record is added or removed in table2, the corresponding counter in table1 should be updated with the new count.

-
Oh, I didn't even think about if the count changed. In my case, that wont be a problem, but it's something I need to be wary of in case i do add another record. I will look it up now. Why do you think triggers should be avoided? –  user40128 Apr 18 '14 at 2:56
Because it's always the last place you think of looking for a bug at when there's a problem! These things are way too easy to forget! –  Mat's Mug Apr 18 '14 at 2:58
I understand, thanks. –  user40128 Apr 18 '14 at 3:04

If you are using Oracle, I would expect it would be complaining about ambiguous columns, as carName exists in two tables. Adding aliases is a good idea:

counter = SELECT COUNT(t2.CARNAME)
FROM Table2 t2
JOIN Table1 t1 ON t2.CarName = t1.CarName


But why are you doing this? If the application needs an up-to-date car count, it can issue the above query on demand. If reporting users need this info, give them a view with a computed column for carCount. It is never a good idea to record data in multiple places - it invariably gets out of date.

-
I'm doing this because I was told to. –  user40128 Apr 19 '14 at 8:23
>> I'm doing this because I was told to - Oh dear, that is a terrible reason. One of our functions in life is to influence business analysts, bosses etc when they are doing stupid things... –  kiwiron May 1 '14 at 9:06
 ALTER TABLE table1
(Select COUNT(table2.car_type)
FROM table2
WHERE (table1.car_type = table2.car_type)
);


That looks like one Query to me, have you run it in the database of your choice?

I don't think I would do it this way personally because I don't know what number I am giving to what record. the records can be in any order.

I would do it with two queries

ALTER TABLE Table1 ADD counter INT NULL


And then I would add the numbers

UPDATE Table1
SET counter = SELECT COUNT(CARNAME)
FROM Table2
JOIN Table1 ON Table2.CarName = Table2.CarName
`

For some reason I am fighting with my self about whether or not this will give you the right answer.

-
Yes, I have tried, mine says error: subquery expressions not allowed here. I have also tried your method, however, it says error line 2: missing expression (SET counter = SELECT COUNT(CARNAME)) I will try putting in a GROUP BY now. Also, I beleieve you meant "JOIN Table1 ON table1.carname = table2.carname"? –  user40128 Apr 18 '14 at 2:42
The group by did not make a difference, so that isn't the problem. –  user40128 Apr 18 '14 at 2:47