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:
Datetypenumber
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 salesreturns
-- --
2008-06-1542
2008-06-1620
2008-06-1720
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,
nbsp; 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` (
nbsp; `sales_id` int(11) NOT NULL auto_increment,
nbsp; `sales_date` datetime NOT NULL default '-00-00
00:00:00',
nbsp; `sales_type` tinyint(4) NOT NULL default '0',
nbsp; PRIMARY KEYnbsp; (`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.
