Query Needed

[vssp]

Hi Friends

I am working to draw a graph . I need to get the data monthly, for example JAN total hours FEB total hours ,

How to write the query get the total values per month?

Advance thanks for your reply.

[Shanti C]

can u post your table structure...

[vssp]

hi

Thanks for your reply

Table structure here

CREATE TABLE IF NOT EXISTS `invoice` (
  `id` int(11) NOT NULL auto_increment,
  `instance_id` int(11) NOT NULL default '0',
  `project_id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `instance_category_tasks_master_id` int(11) NOT NULL default '0',
  `invoice_id` int(11) NOT NULL default '0',
  `hours` double(3,2) NOT NULL default '0.00',
  `date` date NOT NULL default '0000-00-00',
  `pay_rate` double(5,2) NOT NULL default '0.00',
  `bill_rate` double(5,2) NOT NULL default '0.00',
  `billable` enum('yes','no') NOT NULL default 'yes',
  `created_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_ipaddress` varchar(15) NOT NULL default '',
  `updated_date` date NOT NULL default '0000-00-00',
  `updated_ipaddress` varchar(15) NOT NULL default '',
  `active` char(3) NOT NULL default '',
  `timecard_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
)

[Shanti C]

hello vssp...
there is a function called SUM in mysql to calculate total of any numerical column...and if you want to calculate per month,then use limit by 30 days...or created_date < last day of month...
see this link for your reference:
http://www.tizag.com/mysqlTutorial/mysqlsum.php

[vssp]

I need the query for every month , I need the query to get the values every month.

Any Idea

[Shanti C]

hello see this example will help you:
t1 table having 4 columns like year,month,day,points...
year month day points
2000 01 01 1
2000 01 20 3
2000 01 02 5
2000 02 23 10

then here im calculting total point per month...
here is the query:

SELECT year,
MONTH , sum( points ) AS points
FROM t1
GROUP BY year,
MONTH

it will print:
year month points
2000 01 8
2000 02 15

[Shanti C]

i think you have to add one more column like month the you will calculate hours per month by using group by month column...

[vssp]

Cool .Let em check and let you know any doubt

[Shanti C]

hello...have you checked it???
have you got solved your problem...???

[vssp]

Yes I have checked and complete the task .Thanks for you.

[Shanti C]

Welcome...