Mysql – How to group the data in the following format

countgroup byMySQL

Using the following table


OrderDate     OrderID
[dates]       [id]

I want to display in the following format :

quater name   nooforders
q1            10
q2            10
q3            20

I have tried the following query :

select count(order_id)no_of_orders, (case when month(order_date)<=4 then 'q1' when month(order_date)<=8 then 'q2' when month(order_date)<=12 then 'q3' end)quarter from bab_orders group by quarter;

However this query does not show the value of a particular quarter when it has no orders associated with it. How do I display that?

Best Answer

To display the given format, you can use the following queries instead:

For MySQL, use the following:

  SELECT QuaterName, COUNT(OrderID) FROM
    (
        SELECT QUARTER(OrderDate) QuaterName, OrderID from bab_orders
    ) T1 group by QuaterName;

for Oracle use the following:

SELECT QuaterName, COUNT(OrderID) FROM
(
    SELECT TO_CHAR(TO_DATE(OrderDate, 'dd.mm.yyyy'), 'Q') 
              QuaterName, OrderID from bab_orders
) T1 group by QuaterName;

to show always the Quarters even if you don't have a data, execute the following query:

SELECT QRTR, COUNT(OrderID) TheCount FROM
(
  SELECT QUARTER('2014-01-01')  QRTR  
  union
  SELECT QUARTER('2014-04-01')  QRTR
  union
  SELECT QUARTER('2014-08-01')  QRTR
  union
  SELECT QUARTER('2014-12-01')  QRTR
)  QRTR_T
LEFT JOIN 
(
   SELECT QUARTER(OrderDate) Quater, OrderID from bab_orders
) ORDERS
on Quater = QRTR
group by QRTR

OBSERVATION #1

If tablename had no indexes, a full table scan would be required.

OBSERVATION #2

If tablename had an index on rank, you still get a full table scan because of the MySQL Query Optimizer ruling out the use of the index because of factors such as key distribution and the possibility of having to lookup each id for every rank during a full index scan.

OBSERVATION #3

If the table had a compound index of (rank,id), then you can a full index scan. In most cases, a full index scan that never references the table for non-indexed columns would be faster than a full index scan that does (See OBSERVATION #2)

OBSERVATION #4

If the query was written slightly different

SELECT rank, COUNT(1) FROM tablename GROUP BY rank

then an index on just the rank column would suffice and produce a full index scan.

CONCLUSION

In light of these observtions, it is definitely a thing of beauty to present to the MySQL Query Optimizer two things:

a good query
proper indexes for all tables in the query

In retrospect, it is also good to give the MySQL Query Optimizer as much of an advantage upfront as possible.

Need to count records and group count by date on Oracle

Unless I am missing something, your query would be something like this:

select created, count(*) CreatedCount
from yourtable
group by created
order by created;

See SQL Fiddle with Demo

Or if you have a time associated with the date, you can use TRUNC:

select trunc(created), count(*) CreatedCount
from yourtable
group by trunc(created)
order by trunc(created);