#1
  1. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,246
    Rep Power
    220

    How to build this query the right way


    Hi;

    I wanna make a query and show barchart of sales (monthly) from last year to today.

    There are 1300 records and 54 weeks.

    My solution is shetty way because I put sql in loop and phuck it up badly.

    I need a query to list days and count number of sales or SUM of sales per week (or day) and show bar charts like that,

    Please advise on such query.

    Here is the table
    Code:
    CREATE TABLE `myTransactions` (
      `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `Date` date NOT NULL,
      `Time` varchar(255) NOT NULL,
      `Refund Expiration` date NOT NULL,
      `Receipt` varchar(255) NOT NULL,
      `Item` int(10) unsigned NOT NULL,
      `Amount` decimal(10,0) NOT NULL,
      `Quantity` tinyint(3) unsigned NOT NULL,
      `Txn Type` varchar(255) NOT NULL,
      `TID` varchar(255) NOT NULL,
      `Upsell From` varchar(255) NOT NULL,
      `Has Upsells` varchar(255) NOT NULL,
      `Line Item Type` varchar(255) NOT NULL,
      `Pmt` varchar(255) NOT NULL,
      `Currency` varchar(255) NOT NULL,
      `Vendor` varchar(255) NOT NULL,
      `Affiliate` varchar(255) NOT NULL,
      `Commission Attribution` varchar(255) NOT NULL,
      `CC‡` varchar(255) NOT NULL,
      `St.` varchar(255) NOT NULL,
      `Last Name` varchar(255) NOT NULL,
      `First Name` varchar(255) NOT NULL,
      `Display Name` varchar(510) NOT NULL,
      `Email` varchar(510) NOT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1347 DEFAULT CHARSET=utf8;
    Thanks
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,437
    Rep Power
    9645
    If you want results per day then SELECT whatever SUMs and COUNTs and such you want, then GROUP BY Date.

    Results per week (or other interval) is the same with a little more work: use DATE_FORMAT to turn the Date into a week string depending on your rules for what a week is (eg, "%Y%U") then GROUP BY it.

IMN logo majestic logo threadwatch logo seochat tools logo