What would be the easiest way to get the AVG for lic_used by date with data in a table like this:
LIC_USED LIC_DATE 18 10/01/02 03:00:00 16 10/01/02 04:00:00 24 10/02/02 03:00:00
I need to get the AVG(lic_used) for each day.
The answer is to GROUP BY the date part. This means splitting out the date part without the time part. There are several ways to do this, depending on which database system you're using. For example, in Microsoft SQL/Server, you can use the CONVERT function to generate the date part, and group on that:
select avg(lic_used) , convert(char(8),lic_date,10) from yourtable group by convert(char(8),lic_date,10)
Note that I have chosen style 10 for the conversion, which is mm/dd/yy with a two-character year, but only because that's the format you used in your example. (I personally prefer yyyy-mm-dd because it's not ambiguous.) Hence with style 10 the result of the conversion is an 8-character string. Refer to the Microsoft documentation CAST and CONVERT for other styles.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.