PHP MySQL Group By question

I have a column inside my table: tbl_customers that distinguishes a customer record as either a LEAD or a CUS.

The column is simply: recordtype, with is a char(1). I populate it with either C, or L. Obviously C = customer, while L = lead.

I want to run a query that groups by the day the record was created, so I have a column called: datecreated.

Here's where I get confused with the grouping.

I want to display a result (in one query) the COUNT of customers and the COUNT of leads for a particular day, or date range. I'm successful with only pulling the number for either recordtype:C or recordtype:L , but that takes 2 queries.

Here's what I have s开发者_Python百科o far:

  SELECT COUNT(customerid) AS `count`, datecreated 
    FROM `tbl_customers` 
   WHERE `datecreated` BETWEEN '$startdate."' AND '".$enddate."' 
     AND `recordtype` = 'C' 
GROUP BY `datecreated` ASC

As expected, this displays 2 columns (the count of customer records and the datecreated).

Is there a way to display both in one query, while still grouping by the datecreated column?

You can do a group by with over multiple columns.

SELECT COUNT(customerid) AS `count`, datecreated, `recordtype`
FROM `tbl_customers` 
WHERE `datecreated` BETWEEN '$startdate."' AND '".$enddate."' 
GROUP BY `datecreated` ASC, `recordtype`

SELECT COUNT(customerid) AS `count`,
    datecreated,
    SUM(`recordtype` = 'C') AS CountOfC,
    SUM(`recordtype` = 'L') AS CountOfL
FROM `tbl_customers` 
WHERE `datecreated` BETWEEN '$startdate."' AND '".$enddate."' 
GROUP BY `datecreated` ASC

See Is it possible to count two columns in the same query

There are two solutions, depending on whether you want the two counts in separate rows or in separate columns.

In separate rows:

SELECT datecreated, recordtype, COUNT(*)
FROM tbl_customers
WHERE datecreated BETWEEN '...' AND '...'
GROUP BY datecreated, recordtype

In separate colums (this is called pivoting the table)

SELECT datecreated,
       SUM(recordtype = 'C') AS count_customers,
       SUM(recordtype = 'L') AS count_leads
FROM tbl_customers
WHERE datecreated BETWEEN '...' AND '...'
GROUP BY datecreated

Use:

$query = sprintf("SELECT COUNT(c.customerid) AS count,
                         c.datecreated,
                         SUM(CASE WHEN c.recordtype = 'C' THEN 1 ELSE 0 END) AS CountOfC,
                         SUM(CASE WHEN c.recordtype = 'L' THEN 1 ELSE 0 END) AS CountOfL
                    FROM tbl_customers c 
                   WHERE c.datecreated BETWEEN STR_TO_DATE('%s', '%Y-%m-%d %H:%i') 
                                           AND STR_TO_DATE('%s', '%Y-%m-%d %H:%i')
                GROUP BY c.datecreated",
                  $startdate, $enddate);

You need to fill out the date format - see STR_TO_DATE for details.