Off the top of my head I can't think of a way of getting the output in the format that you want.
If you use a query like: SELECT SUBSTRING(sales_date,1,10), sales_type, COUNT(sales_id) FROM sales_activity GROUP BY SUBSTRING(sales_date,1,10), sales_type; You'll get output like: Date type number 2008-06-15 1 4 2008-06-15 2 2 2008-06-16 1 2 2008-06-17 1 2 which is the data that you want in a different output format. Thanks On Mon, 2008-06-23 at 18:16 -0700, Grant Giddens wrote: > Ian, > > Thanks for the help, this query worked perfectly. Can you also help > me with one more query? Say my sales_type=1 for a sale, and > sales_type=2 for a return. I'd like to do 1 query to get a count of > the sales and returns for each day. Here was my test data again: > > INSERT INTO `sales_activity` VALUES (1, '2008-06-15 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (2, '2008-06-15 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (3, '2008-06-15 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (4, '2008-06-15 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (5, '2008-06-15 13:00:00', 2); > INSERT INTO `sales_activity` VALUES (6, '2008-06-15 13:00:00', 2); > INSERT INTO `sales_activity` VALUES (7, '2008-06-16 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (8, '2008-06-16 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (9, '2008-06-17 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (10, '2008-06-17 13:00:00', 1); > > The result of the query should be: > > date sales returns > ------ -------- ---------- > 2008-06-15 4 2 > 2008-06-16 2 0 > 2008-06-17 2 0 > > Thanks, > Grant > > --- On Wed, 6/18/08, Ian Simpson <[EMAIL PROTECTED]> wrote: > From: Ian Simpson <[EMAIL PROTECTED]> > Subject: Re: Do I need to use GROUP BY to do this? > To: "Grant Giddens" <[EMAIL PROTECTED]> > Cc: mysql@lists.mysql.com > Date: Wednesday, June 18, 2008, 11:02 AM > > > > I happen to have worked on a similar query this morning, so > it's in my > mind :) > > SELECT SUBSTRING(sales_date,1,10), COUNT(sales_id) > FROM sales_activity > WHERE sales_type = 1 > GROUP BY SUBSTRING(sales_date,1,10); > > should do the trick. > > On Tue, 2008-06-17 at 18:21 -0700, Grant Giddens wrote: > > Hi, > > > > I have a table where I keep sales transactions, so > I'm trying to do a query that will count the number of > transactions per day. > > > > My test data looks like: > > > > -- > > -- Table structure for table `sales_activity` > > -- > > > > CREATE TABLE `sales_activity` ( > > `sales_id` int(11) NOT NULL auto_increment, > > `sales_date` datetime NOT NULL default '0000-00-00 > 00:00:00', > > `sales_type` tinyint(4) NOT NULL default '0', > > PRIMARY KEY (`sales_id`) > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; > > > > -- > > -- Dumping data for table `sales_activity` > -- > Ian Simpson > System Administrator > MyJobGroup > > > This email may contain confidential information and is > intended for the recipient(s) only. If an addressing or > transmission error has misdirected this email, please notify > the author by replying to this email. If you are not the > intended recipient(s) disclosure, distribution, copying or > printing of this email is strictly prohibited and you should > destroy this mail. Information or opinions in this message > shall not be treated as neither given nor endorsed by the > company. Neither the company nor the sender accepts any > responsibility for viruses or other destructive elements and > it is your responsibility to scan any attachments. -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
