Announcement

Collapse
No announcement yet.

Grouping by Day of week

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Grouping by Day of week

    Hi,

    I have a db table with a date field, called {date}. I have been able to create a new field (not found in my table) and display the DAY of week.

    So in my grid I have

    Customer, Date, DAY.

    Now, how do I group by DAY?

    I need a solution for that.

    Thanks beforehand.

  • #2
    Grouping by a custom field.

    Comment


    • #3
      In your SQL statement: SELECT * FROM your_table GROUP BY weekday(date_field)
      Oh, btw name your field different than just date. Sooner or later you'll run into trouble.

      jsb
      Last edited by jsbinca; 01-31-2014, 04:26 PM.

      Comment


      • #4
        Thanks

        Originally posted by jsbinca View Post
        In your SQL statement: SELECT * FROM your_table GROUP BY weekday(date_field)
        Oh, btw name your field different than just date. Sooner or later you'll run into trouble.

        jsb
        Thanks.

        Now, too late to change the field name in current app, as it is used too many places!

        Comment


        • #5
          Originally posted by jsbinca View Post
          In your SQL statement: SELECT * FROM your_table GROUP BY weekday(date_field)
          Oh, btw name your field different than just date. Sooner or later you'll run into trouble.

          jsb
          Hello jsbinca,

          Thanks again.

          I placed the GROUP BY clause, great, but have following problem:

          Lets say I have data for Sun, Thu, Sun, Thu, Thu, Thu.

          In the grid I have only two rows: Sun, Thu.

          How to get the details of each (inside Sun and Thu)

          Thanks for replyiing.

          Comment


          • #6
            Well, that's what grouping does.
            You probably need an ORDER BY. Substitute the GROUP BY clause by an ORDER BY clause.
            See if this is closer to what you want.

            Comment


            • #7
              Originally posted by jsbinca View Post
              Well, that's what grouping does.
              You probably need an ORDER BY. Substitute the GROUP BY clause by an ORDER BY clause.
              See if this is closer to what you want.
              Got it:

              I did some manipulation like that:

              In SQL:
              SELECT
              customer,
              `date`,
              weekday(date) as week_day,
              `time`,
              comment
              FROM
              tableofdata

              Then got week_day as a field and did a grouping on week_day then time.

              Now I have by Grid display like that:

              Week Day => 3
              0830
              Customer1
              Customer7
              1100
              Customer6
              Week Day => 6
              1000
              Customer207
              Customer103
              Customer92
              1500
              Customer8


              Now what do I need to do to replace the 3 by Thursday. Where do I put the code, select case?

              Thanks for reply.

              Comment


              • #8
                Sorry, all indentation gone away.

                Comment


                • #9
                  Ok, that's a slightly different:

                  SELECT
                  customer,
                  `date`,
                  DATE_FORMAT(date,'%W') as week_day,
                  `time`,
                  comment
                  FROM
                  tableofdata

                  Comment


                  • #10
                    Hello,

                    Thanks for finding a great solution for me.

                    Comment

                    Working...
                    X