Query Needed

Thread Solved
1 2

•
•
Join Date: Jul 2006
Posts: 197
Reputation: vssp has a little shameless behaviour in the past 
Solved Threads: 5
vssp vssp is offline Offline
Junior Poster

Query Needed

 
0
  #1
Sep 10th, 2008
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.
Thanks
VSSP
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 1,076
Reputation: Shanti Chepuru is on a distinguished road 
Solved Threads: 98
Shanti Chepuru's Avatar
Shanti Chepuru Shanti Chepuru is offline Offline
Veteran Poster

Re: Query Needed

 
0
  #2
Sep 10th, 2008
can u post your table structure...
Be intelligent, But Don't try to cheat.. Be innocent But Don't get cheated..
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2006
Posts: 197
Reputation: vssp has a little shameless behaviour in the past 
Solved Threads: 5
vssp vssp is offline Offline
Junior Poster

Re: Query Needed

 
0
  #3
Sep 10th, 2008
hi

Thanks for your reply

Table structure here

sql Syntax (Toggle Plain Text) 
  1. CREATE TABLE IF NOT EXISTS `invoice` (
  2.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `instance_id` INT(11) NOT NULL DEFAULT '0',
  4.   `project_id` INT(11) NOT NULL DEFAULT '0',
  5.   `user_id` INT(11) NOT NULL DEFAULT '0',
  6.   `instance_category_tasks_master_id` INT(11) NOT NULL DEFAULT '0',
  7.   `invoice_id` INT(11) NOT NULL DEFAULT '0',
  8.   `hours` DOUBLE(3,2) NOT NULL DEFAULT '0.00',
  9.   `DATE` DATE NOT NULL DEFAULT '0000-00-00',
  10.   `pay_rate` DOUBLE(5,2) NOT NULL DEFAULT '0.00',
  11.   `bill_rate` DOUBLE(5,2) NOT NULL DEFAULT '0.00',
  12.   `billable` ENUM('yes','no') NOT NULL DEFAULT 'yes',
  13.   `created_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  14.   `created_ipaddress` VARCHAR(15) NOT NULL DEFAULT '',
  15.   `updated_date` DATE NOT NULL DEFAULT '0000-00-00',
  16.   `updated_ipaddress` VARCHAR(15) NOT NULL DEFAULT '',
  17.   `active` CHAR(3) NOT NULL DEFAULT '',
  18.   `timecard_id` INT(11) NOT NULL,
  19.   PRIMARY KEY  (`id`)
  20. )
Last edited by peter_budo; Sep 11th, 2008 at 5:40 am. Reason: Keep It Organized - please use [code] tags
Thanks
VSSP
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 1,076
Reputation: Shanti Chepuru is on a distinguished road 
Solved Threads: 98
Shanti Chepuru's Avatar
Shanti Chepuru Shanti Chepuru is offline Offline
Veteran Poster

Re: Query Needed

 
0
  #4
Sep 10th, 2008
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
Be intelligent, But Don't try to cheat.. Be innocent But Don't get cheated..
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2006
Posts: 197
Reputation: vssp has a little shameless behaviour in the past 
Solved Threads: 5
vssp vssp is offline Offline
Junior Poster

Re: Query Needed

 
0
  #5
Sep 10th, 2008
I need the query for every month , I need the query to get the values every month.

Any Idea
Thanks
VSSP
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 1,076
Reputation: Shanti Chepuru is on a distinguished road 
Solved Threads: 98
Shanti Chepuru's Avatar
Shanti Chepuru Shanti Chepuru is offline Offline
Veteran Poster

Re: Query Needed

 
0
  #6
Sep 10th, 2008
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) 
  1. SELECT YEAR,
  2. MONTH , sum( points ) AS points
  3. FROM t1
  4. GROUP BY YEAR,
  5. MONTH
it will print:
year month points
2000 01 8
2000 02 15
Be intelligent, But Don't try to cheat.. Be innocent But Don't get cheated..
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 1,076
Reputation: Shanti Chepuru is on a distinguished road 
Solved Threads: 98
Shanti Chepuru's Avatar
Shanti Chepuru Shanti Chepuru is offline Offline
Veteran Poster

Re: Query Needed

 
0
  #7
Sep 10th, 2008
i think you have to add one more column like month the you will calculate hours per month by using group by month column...
Be intelligent, But Don't try to cheat.. Be innocent But Don't get cheated..
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2006
Posts: 197
Reputation: vssp has a little shameless behaviour in the past 
Solved Threads: 5
vssp vssp is offline Offline
Junior Poster

Re: Query Needed

 
0
  #8
Sep 10th, 2008
Cool .Let em check and let you know any doubt
Thanks
VSSP
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 1,076
Reputation: Shanti Chepuru is on a distinguished road 
Solved Threads: 98
Shanti Chepuru's Avatar
Shanti Chepuru Shanti Chepuru is offline Offline
Veteran Poster

Re: Query Needed

 
0
  #9
Sep 12th, 2008
hello...have you checked it???
have you got solved your problem...???
Be intelligent, But Don't try to cheat.. Be innocent But Don't get cheated..
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2006
Posts: 197
Reputation: vssp has a little shameless behaviour in the past 
Solved Threads: 5
vssp vssp is offline Offline
Junior Poster

Re: Query Needed

 
0
  #10
Sep 15th, 2008
Yes I have checked and complete the task .Thanks for you.
Thanks
VSSP
Reply With Quote Quick reply to this message  
Reply
1 2

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MySQL Forum


Views: 1161 | Replies: 10
Thread Tools Search this Thread



Tag cloud for MySQL
"use" 1 agplv3 alfresco amazon api artisticlicense breathalyzer camparingtocolumns changingprices cmg contentmanagement count court crm data database design developer development drupal dui ec2 eliminate email enter enterprise error eudora facebook form foss gartner gnu gpl greenit groklaw groupware images innerjoins insert ip joebrockmeier join journalism keywords laptop law legal license licensing linux maintenance managing matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource operand oracle pdf penelope php priceupdating referencedesign remove reorderingcolumns results resultset saas select sharepoint sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency update virtualization
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC