Member Avatar for almahmeed

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.

Regards,

Aziz

  • 2 Contributors
  • 1 Reply
  • 82 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by rch1231
Member Avatar for rch1231

Hello,

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.