Help with AVG in Query with Left Join
I need help to write sql query to know avg of position.
SELECT p.date_add, p.pozycja, f.nazwa FROM fraza f
LEFT JOIN pozycja p ON f.id = p.parent_id
WHERE f.parent_id = 101 AND p.date_add BETWEEN '2010-12-01' AND '2011-01-01'
ORDER BY f.nazwa DESC, p.date_add ASC
LIMIT 1000
now i got something like this:
date_add | pozycja(position) | nazwa(name)
2010-12-01 12 example1
2010-12-01 12 example2
2010-12-01 12 example3
2010-12-01 12 example4
2010-12-01 12 example5
2010-12-02 36 example1
2010-12-02 36 example2
2010-12-02 36 example3
2010-12-02 36 example4
2010-12-02 36 example5
how to get now something like this:
posisiton | name
24 example1
24 example2
24 example3
开发者_JS百科 24 example4
24 example5
its avg of month name's position
SELECT p.date_add, avg(p.pozycja) as avg_pozycja, f.nazwa FROM fraza f
LEFT JOIN pozycja p ON f.id = p.parent_id
WHERE f.parent_id = 101 AND p.date_add BETWEEN '2010-12-01' AND '2011-01-01'
GROUP BY f.nazwa
ORDER BY f.nazwa DESC, p.date_add ASC
精彩评论