Help Needed in writing SQL

Analytics
Enthusiast

Help Needed in writing SQL

Hi All,

I have the below data in 1 table :

Log Date Tablename Space Occupied(GB)
1-Jul-11 Employee 1
2-Jul-11 Employee 2
3-Jul-11 Employee 3
… …
… …
… …
31-Jul-11 Employee 5
1-Jun-11 Dept 6
2-Jun-11 Dept 7
3-Jun-11 Dept 8
… …
… …
… …
30-Jun-11 Dept 10

Now I want to write a query so as to produce the output like below :

Log Date TableName Space Occupied(GB)
Jul-11 Employee Avg of all the spaces for July Month
Jun-11 Dept Avg of all the spaces for June Month

Could you please tell me what will be sql query to achieve this result set ..Thanks in advance ..

Regards,
Ansh
2 REPLIES
Junior Contributor

Re: Help Needed in writing SQL

select
extract(year from logdate),
extract(month from logdate),
tablename,
avg(space)
from tab
group by 1,2,3

Dieter

Re: Help Needed in writing SQL

Ansh,

Another way of doing the same..

Select

Logdate (DATE, FORMAT 'mmm-yy-dd') (CHAR(6)),

Tablename,

Avg(space)

From Table

Group By 1,2,3;

Thanks

Mohankumar