Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Unanswered: [query]Group by month,year,quarter

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •