Grouping by Day of week

Applications Grid / Reports
#1

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.

#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

#4

Thanks

[QUOTE=jsbinca;21090]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[/QUOTE]

Thanks.

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

#5

[QUOTE=jsbinca;21090]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[/QUOTE]

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.

#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.

#7

[QUOTE=jsbinca;21095]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.[/QUOTE]

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.

#8

Sorry, all indentation gone away.

#9

Ok, that’s a slightly different:

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

#10

Hello,

Thanks for finding a great solution for me.