Ad:

MySQL Discussion Thread
Marked Solved
Views: 1530

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Sep 10th, 2008

Query Needed

Expand Post »

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.

Similar Threads

Volunteer C++ programmer needed for in C++
Secure MP3 Downloads ?? Help Needed in PHP

vssp

Reputation Points: 5

Solved Threads: 5

Junior Poster

Offline

197 posts
since Jul 2006

Permalink

Sep 10th, 2008

Re: Query Needed

can u post your table structure...

Shanti C

Reputation Points: 137

Solved Threads: 162

Posting Virtuoso

Offline

1,641 posts
since Jul 2008

Permalink

Sep 10th, 2008

Re: Query Needed

hi

Thanks for your reply

Table structure here

sql Syntax (Toggle Plain Text)
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`)
)
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`)
)

Last edited by peter_budo; Sep 11th, 2008 at 5:40 am. Reason: Keep It Organized - please use [code] tags

vssp

Reputation Points: 5

Solved Threads: 5

Junior Poster

Offline

197 posts
since Jul 2006

Permalink

Sep 10th, 2008

Re: Query Needed

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

Shanti C

Reputation Points: 137

Solved Threads: 162

Posting Virtuoso

Offline

1,641 posts
since Jul 2008

Permalink

Sep 10th, 2008

Re: Query Needed

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

Any Idea

vssp

Reputation Points: 5

Solved Threads: 5

Junior Poster

Offline

197 posts
since Jul 2006

Permalink

Sep 10th, 2008

Re: Query Needed

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:

mysql Syntax (Toggle Plain Text)
SELECT YEAR,
MONTH , sum( points ) AS points
FROM t1
GROUP BY YEAR,
MONTH
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

Reputation Points: 137

Solved Threads: 162

Posting Virtuoso

Offline

1,641 posts
since Jul 2008

Permalink

Sep 10th, 2008

Re: Query Needed

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

Shanti C

Reputation Points: 137

Solved Threads: 162

Posting Virtuoso

Offline

1,641 posts
since Jul 2008

Permalink

Sep 10th, 2008

Re: Query Needed

Cool .Let em check and let you know any doubt

vssp

Reputation Points: 5

Solved Threads: 5

Junior Poster

Offline

197 posts
since Jul 2006

Permalink

Sep 12th, 2008

Re: Query Needed

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

Shanti C

Reputation Points: 137

Solved Threads: 162

Posting Virtuoso

Offline

1,641 posts
since Jul 2008

Permalink

Sep 15th, 2008

Re: Query Needed

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

vssp

Reputation Points: 5

Solved Threads: 5

Junior Poster

Offline

197 posts
since Jul 2006

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: SQL help - flat select

Next Thread in MySQL Forum Timeline: Database import in Cpanel 11

DaniWeb Message

Cancel Changes

User Name
Password