Need mysql query to add values for each day in a time series... Need help

I have a transaction table and I'm looking to generate a dataset to drive a line chart that shows the sum of all the sales happened on each day during a given period. I have never grouped results like that before and am scratching my head.

Let's say the table is called "transactions", the "datetime" field is called timestamp, and the sales amount on each transaction is "txn_amount". I want the result set to include each day: "1/2/10" and the sum of the transaction amounts.

I need to get a book and spend a week learning mysql... Thanks for helping me out.

This question and answers originated from www.stackoverflow.com
Question by (3/5/2010 4:52:18 AM)

Answer

 
select sum(txn_amount) ,timestamp from transactions where timestamp in (select distinct timestamp from transactions) group by timestamp

if datatype is datetime,Use this

select sum(amt) ,substring(dt,1,10) from transactions where substring(dt,1,10) in (select distinct substring(dt,1,10) from transactions) group by substring(dt,1,10)
Answer by
Find More Answers
Related Topics  mysql
Related Questions

  • I need help with a MySQL query

    Ok I have a table in my mysql query browser like shown below: NAME: Jobs: Kate Contractor John Janitor Bob Writer Don Waitress Let's say I want replace the job of Kate …

  • Need help on a MySQL search query

    I need some help on a MySQL query I'm trying to set up. I need to find records that match conditions that are located in two different tables with a many to many relationship. I use three tables …

  • Need Help with a mysql query

    I have 2 table first table. employee( empid- primary key ,empname) eg: employee table 1 john 2 martyn 3 davis second table documents(empid,documentname) eg : 1 address.doc 1 work…

  • Need help with a mysql query

    I've developed a mysql query that tells me how many times each product was purchased over a specified timeframe. I'm trying to figure out how to join this with my pageview tracking table so I can ca…

  • Need help with a mysql query

    I have a payments table that has the following structure. **Payments** id name created_by - user_id closed_by - user_id **Users** user_id name surname What is the best way to show both the n…

  • Need help with a MySQL query

    Please help me with this MySQL query. I've been on it for long enough. Perhaps it needs a fresh pair of eyes. Two tables: locks and sessions locks -------------- id session_id -------------- 1…

  • Need help in building a MySQL query

    I have one table that contains two columns: item ID item Name I have another products table that has three columns: product name item id 1 item id 2 I want to build a query…

  • I need to simplify a MySQL sub query for performance - please help

    I have the following query which is takin 3 seconds on a table of 1500 rows, does someone know how to simplify it? SELECT dealers.name, dealers.companyName, dealer_accounts.balance FROM dealers …

  • mySQL in need of a more efficient way to check for and add values to database

    I have two variables, UserID, and User_Links, As example, UserID = 10098729 User_Links = 10432,78723,78263,21829,759273,108797,21167 (this is a string) The table I am considering is set…

  • Need help constructing MySQL Query

    My data & table is as follows: +------+------------+-----------+--------------+------------+----------+-------------+ | sr | thread_id | thread_no | client_name | date | time | status …