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.