I have a problem of creating mysql that return a single value, calculated from values in a column.what kinds of function i think to use?
you can try this some of fuctions;
Useful aggregate functions:
AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum
it depend on the problem you want to solve :)
What you are looking for is probably the group by clause. Group By consolidates the results for a unique value of one of the data fields. Below is an example of two tables joined and results given using group by.
To display the artists by name rather than ID numbers, join the painting table to the artist table:
SELECT artist.name AS painter, COUNT(painting.a_id) AS count FROM artist, painting WHERE artist.a_id = painting.a_id GROUP BY artist.name; +----------+-------+ | painter | count | +----------+-------+ | Da Vinci | 2 | | Renoir | 1 | | Van Gogh | 3 | +----------+-------+