user2145673 user2145673 - 4 years ago 110
SQL Question

get count of items per category and insert into another table

I am struggling with an issue which I believe some of you will be quite easy to resolve.

I have two tables, one which holds items of cars objects with their respective categories, each car make has its own category.

The second table, holds the same list of categories and another column that should hold the total number of car items per category. e.g carCategory=30, TotalItems=6.

I was able to get the count statement running however i was unable to find the correct way to get the values from the count statement on the first table and place them on the second table in their respective category.

Any suggestions how can I solve this one?

Here are the tables
"cars_for_sell" table:


+------+------------+--------------+-------------+-------------+-------------------+------------+------------------+------------------+----------------+-----------------+
| "id" | "car_id" | "car_vendor" | "car_model" | "car_petro" | "car_transmition" | "car_hand" | "car_spedometer" | "car_engine_vol" | "car_category" | "car_post_date" |
+------+------------+--------------+-------------+-------------+-------------------+------------+------------------+------------------+----------------+-----------------+
| "39" | "33333333" | "Mazda" | "3" | "Disel" | "automatic" | "3" | "1111111" | "1600" | "30" | "2/20/2002" |
| "49" | "6666666" | "Toyota" | "Avensis" | "Gasolin" | "manual" | "6" | "2222222" | "1800" | "10" | "8/12/2015" |
+------+------------+--------------+-------------+-------------+-------------------+------------+------------------+------------------+----------------+-----------------+


"posts_per_category" table:


+------------+--------------+--------------+------------------+
| "category" | "carMake" | "NumOfPosts" | "LastUpdateDate" |
+------------+--------------+--------------+------------------+
| "10" | "BMW" | "0" | "12/24/2015" |
| "20" | "MG" | "0" | "12/24/2015" |
| "30" | "AUDI" | "0" | "12/24/2015" |
| "40" | "OPEL" | "0" | "12/24/2015" |
| "50" | "ALFA_ROMEO" | "0" | "12/24/2015" |
| "60" | "BUICK" | "0" | "12/24/2015" |
| "70" | "DACIA" | "0" | "12/24/2015" |
| "80" | "HONDA" | "0" | "12/24/2015" |
| "90" | "VOLVO" | "0" | "12/24/2015" |
| "100" | "TOYOTA" | "0" | "12/24/2015" |
| "110" | "HYUNDAI" | "0" | "12/24/2015" |
| "120" | "LANCIA" | "0" | "12/24/2015" |
| "130" | "MAZDA" | "0" | "12/24/2015" |
| "140" | "MITSUBISHI" | "0" | "12/24/2015" |
| "150" | "NISSAN" | "0" | "12/24/2015" |
| "160" | "SUBARU" | "0" | "12/24/2015" |
| "170" | "SUZUKI" | "0" | "12/24/2015" |
| "180" | "SEAT" | "0" | "12/24/2015" |
| "190" | "CITROEN" | "0" | "12/24/2015" |
| "200" | "SKODA" | "0" | "12/24/2015" |
| "210" | "VOLKSWAGEN" | "0" | "12/24/2015" |
| "220" | "FORD" | "0" | "12/24/2015" |
| "230" | "FIAT" | "0" | "12/24/2015" |
| "240" | "PEUGEOT" | "0" | "12/24/2015" |
| "250" | "KIA" | "0" | "12/24/2015" |
| "260" | "CHRYSLER" | "0" | "12/24/2015" |
| "270" | "RENAULT" | "0" | "12/24/2015" |
| "280" | "CHEVROLET" | "0" | "12/24/2015" |
+------------+--------------+--------------+------------------+



Here is the result I am getting from count statement:


+----+---+
| 10 | 1 |
+----+---+
| 30 | 1 |
+----+---+


So once I have the result above I need that "posts_per_category" will have the updated results stored in "NumOfPosts" column according to their respective categories

Answer Source

If i understand your question correctly, you should do something like:

UPDATE posts_per_category ppc SET ppc.numOfPosts= (SELECT count(id) FROM cars_for_sell cfs WHERE ppc.category = cfs.car_category)

But I think that this method is not right. Maybe you should to think about using triggers (you can create "events" which will fire when new cars will be added and update ppc.NumOfPosts, for example).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download