Use times times and multiply it by the number in tables in SQL
I am doing a school assignment that has to do with gifts and the timing of their production. We have a gift table that contains the name, gift_number and gift_production_time, and a wishes table that contains gift_number, wish_number, and person_number. With these tables, we have to calculate the amount of time it takes for all the gifts to be made in minutes and days.
Seeing that this is an introductory course for databases, I ended up in the notebook for this task. The closest I can get is to have a line for each gift, showing the production times for each one individually, but not the total amount of time it takes.
Here is my closest attempt:
SELECT w.gift_number, count(w.gift_number)*production_time as total_minutes
FROM wishes as w, gifts as g
WHERE g.gift_number = w.gift_number
GROUP BY w.gift_number
I don't think I can get the correct answer with the GROUP BY operator, but the math is wrong if I omit it. Any help is appreciated. :-)
EDIT
Gift table
|gift_number | gift_name | production_time
|_________________________________________
|1 gift1 130
|2 gift2 140
|3 gift3 200
|4 gift4 100
Wish table
|wish_number | person_number | gift_number |
|___________________________________________
|1 1 2
|2 1 4
|3 2 2
|4 3 1
source to share
First, if you are learning SQL, you must learn the correct syntax join
. As a simple rule of thumb, never use commas in a sentence from
.
Second, the query you requested is incorrect from a SQL perspective. MySQL accepts it, but the problem is production_time
. It is not in an aggregation function (like sum()
or min()
), and this is not the case for a sentence group by
. So you are using the MySQL extension. And you should only use this extension when you really understand what you are doing.
I suspect the requested query is more like this:
SELECT sum(production_time) as total_minutes
FROM wishes w JOIN
gifts g
ON g.gift_number = w.gift_number;
Depending on the time of the view (integer minutes? Like time? Etc.) and how you want to see the results, you might need to use date and time functions. I suggest you review them here and play with them to get the results you want .
source to share