Hi Everybody,

I have two tables in my database (mySQL). The 1st table records the data entry of the employee with the following columns:

1. msisdn: Mobile Number
2. fname: Full Name
3. cpr: National ID
4. username: Username (of my data entry staff)
5. createdate: Record Create Date

The 2nd Table is the users table with the following columns

1. username
2. fullname
3. password
4. group

I wanted to generate a report that count the total entries per group (report columns) per day. So, if I assume that I have 20 entries for 7 users from 3 different groups and the entries were made in different periods in 3 days, i should have a report with 4 columns.

1. Date (not the time stamp)
2. Group 1 Count
3. Group 2 Count
4. Group 3 Count

Currently, i have 45 users from 4 different groups with about 56,000 entries. i want to generate the report without impacting the performance of the database.

Appreciate your comments and help.



6 Years
Discussion Span
Last Post by rch1231


This will probably need a little work but it should go something like:

SELECT table1.createdate, table2.group, count(table2.group)
FROM table1 inner join table2 on table1.username=table2.username
group by table1.createdate, table2.group

but that is just off the top of my head.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.