Report totals for non-numerical data

Forum|Forum|17 years ago
July 12, 2009
9 replies
1124 views

Hello all,

I am trying to update a site for an auto shop. The site is in PHP, MySQL.

What they have in the database is records for each customer that comes in to their shop. So, they will have John Q. Public having multiple entries for the date he came in, and what was done to the car. They want a report that will display the totals for each item that John Public has had done.

So for example the report would be displaying the following:

breaks oil change shocks tune up total visits

John Public 2 4 0 2 8

Jack Private 1 6 4 8 19

The database has this information in it to get the above report:

Name Date Work Cust. Review

John Public 02-05-2007 breaks satisfied

John Public 03-15-2008 breaks satisfied

John Public 01-09-2008 oil change satisfied

Is there a way to do this?

Server side applications

This topic is closed to new replies. Start a new post to keep the conversation going.

A

Anonymous

Use the mysql_num_rows() function from SELECT query. This will show the total record returned.

B

bregent

Participating Frequently

This actually is numeric data - you are talking about counts. The basic SQL for this would be

SELECT count(*), work, name from mytable

Group by work, name

The problem is the report format you want. You have pivoted basic sql results into a crosstab. Some DMBS do support crosstab queries natively, but I do not believe this is true of MySQL. So now you will either need to use PHP to format the recordset above as a crosstab, or use a more complicated database schema. The PHP solution would involve looping through the recordset, evaluate the values, and create a corresponding html table column.

Creating a crosstab using SQL technique can be found here:

http://dev.mysql.com/tech-resources/articles/wizard/index.html

Or maybe there is a DW Extension that can perform this.

Good luck.

9thRegAuthor

Known Participant

Thank you very much bregent. That link was very helpful. Do you happon to know another place I can go to add a little bit of complexity to it? For example if I wanted to do the same thing, but have some of the data as foreign keys (for example the work be a foreign key); can that be done? Thanks again.

B

bregent

Participating Frequently

Sure, you can use more than one table. In the example provided it is:

SELECT location, SUM(IF(gender='M',1,0)) AS M,
     SUM(IF(gender='F',1,0)) AS F, COUNT(*) AS total
     FROM locations INNER JOIN employees USING (loc_code) GROUP BY location;

So you just need to substitute your table names, columns, and join conditions and include the SUM expression for each repair job you want to include in the report.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.