[query]Group by month,year,quarter

TheWock · #1 (**permalink**) 12-02-05, 06:01

Hi,

I´m using a program called ´intelliview designer´.
In the program you can select all the data from a mysql table (with a select * from table query) after that you can use a WYSIWYG way to ´group by´ the dates by week, quarter, month and year. Unfortunately, the program doesn´t display the query.

I know how to order all the information for every day and for every employee. (see below)

EG: In order to get the amount of orders for every employee for every day (in mysql) you can use:
Select employee, date, count(*) as amount
from orders
group by employee, date

My questiuon is, how can you get the amount of orders for every employee for every week, month, year etc?

madafaka · #2 (**permalink**) 12-02-05, 06:50

I didn't test it but it should be straightforward

week:

Code:

Select 
  employee, 
  date_format(date, '%U') as Week,
  count(*) as Amount
from orders
group by employee, date_format(date, '%U')

month:

Code:

Select 
  employee, 
  date_format(date, '%M') as Month,
  count(*) as Amount
from orders
group by employee, date_format(date, '%M')

year:

Code:

Select 
  employee, 
  date_format(date, '%Y') as Year,
  count(*) as Amount
from orders
group by employee, date_format(date, '%Y')

all:

Code:

Select 
  employee, 
  date_format(date, '%U') as Week,
  date_format(date, '%M') as Month,
  date_format(date, '%Y') as Year,
  count(*) as Amount
from orders
group by 
  employee, 
  date_format(date, '%U'),
  date_format(date, '%M'),
  date_format(date, '%Y')

and so on...

Internet Services

Web Development

Digital Marketing

Consumer Tech