Do I need to use GROUP BY to do this?

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) NT NULL auto_increment,
  `sales_date` datetime NT NULL default '0000-00-00 00:00:00',
  `sales_type` tinyint(4) NT NULL default '0',
  PRIMARY KEY  (`sales_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTINCREMENT=11 ;

--
-- Dumping data for table `sales_activity`
--

INSERT INT `sales_activity` VALUES (1, '2008-06-15 13:00:00', 1);
INSERT INT `sales_activity` VALUES (2, '2008-06-15 13:00:00', 1);
INSERT INT `sales_activity` VALUES (3, '2008-06-15 13:00:00', 1);
INSERT INT `sales_activity` VALUES (4, '2008-06-15 13:00:00', 1);
INSERT INT `sales_activity` VALUES (5, '2008-06-15 13:00:00', 2);
INSERT INT `sales_activity` VALUES (6, '2008-06-15 13:00:00', 2);
INSERT INT `sales_activity` VALUES (7, '2008-06-16 13:00:00', 1);
INSERT INT `sales_activity` VALUES (8, '2008-06-16 13:00:00', 1);
INSERT INT `sales_activity` VALUES (9, '2008-06-17 13:00:00', 1);
INSERT INT `sales_activity` VALUES (10, '2008-06-17 13:00:00', 1);
       

I would like to get a count of the number of transactions where transaction id=1 for each date.

ie, the result set should look like:

2008-06-15    4
2008-06-16    2
2008-06-17    2


What type of query do I need to get that information?

Thanks!

I happen to have worked on a similar query this morning, so it's in my
mind :)

SELECT SUBSTRING(sales_date,1,10), ****(sales_id)
FRM sales_activity
WHERE sales_type = 1
GRUP BY SUBSTRING(sales_date,1,10);

should do the trick.

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) NT NULL auto_increment,
  `sales_date` datetime NT NULL default '0000-00-00 00:00:00',
  `sales_type` tinyint(4) NT NULL default '0',
  PRIMARY KEY  (`sales_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTINCREMENT=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.