Good day!
First of all, I am new at MySQL, and I've got some problems with altering tables with query's.
Secondly, sorry for bad English, unfortunately, it's not my native language.
So yeah, I have some questions, and I will be very thankful if you will be able to help me.
I have 2 tables: Screenshot
Foreign key is 'manufact' from table Registred
So, I have few Query tasks I wanted to do, but can't figure out how to do them.
Here goes first task I did, I just want you guys to check, If I did it right.
Calculate fields from January and February where Car Manufacturer is VW.
SET Total = January + February WHERE Manufact = 3;
And here comes Query's I can't do:
Here is what I came up with.
DELETE FROM Registered Order by January Limit 1;
But it didn't delete information from table 'manufacturer'.
What can I do there?
Code:
ALTER TABLE Registered Add Column alltotal Int;
UPDATE Registered
Set alltotal = Select SUM(February + January) From Registered;
What I wanted to do is, Create only 1 field, where Sum of all February and January fields will be calculated.
Any suggestions so far?
P.S If I will be able to do these, I will be able to complete other tasks myself :)
P.S.2 I am new here, so please sorry for bad Question editing. I am doing as in tutorial I found but it's hellish for me. I tried my best :)
Let's break this down problem by problem. The first one is:
Delete all information about car manufacturer, that had smallest amount of cars registered in January.
The first thing we need to do is write a subquery that determines which manufacturer that is. We can do so by selecting the id of the row in registracija
with the minimum value for January. We can do that using ORDER BY
, which you caught on to:
SELECT vieglas
FROM registracija
ORDER BY january
LIMIT 1;
Now that we have that id, we can delete from that table using the WHERE
clause:
DELETE FROM registracija
WHERE vieglas = (SELECT vieglas FROM (SELECT * FROM registracija) t ORDER BY january LIMIT 1);
For information on why I included the SELECT * FROM registracija
, see this answer.
To see an SQL Fiddle of that in action, check this: http://sqlfiddle.com/#!9/c81d5/1
For the second part:
Calculate all cars registred in January and February and save it in additional field. (Should be displayed as Int, without floating point.)
We can use the addition operator along with an update command to put this total inside the Total
column like this:
UPDATE registracija
SET total = (january + february);
For the SQL Fiddle of the update, see this: http://sqlfiddle.com/#!9/f5b28/1
for deleting all record which has had smallest amount of cars registred in January delete from 'Registracija' where 'January' =select min('January') from Registracija
Try following:
1) Total Count For January & February
SELECT (January+February) AS TOTAL from Registrant where Vieglas = 3
2) Delete Query
DELETE FROM Registrant INNER JOIN Vieglas Order By Registrant.January Limit 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With