Generating report chart, with count by day of week.

[guyfromfl]

I am at work and keep getting interupted by less important things, like the real world so please for give me if this isn't the clearest post.

 

I am trying to generate report that looks like this:

 

EngineMonTueWedThuFriTotal

Product 1102036

Product 2001124

Repaired1031510

 

 

I have the select of each product that was repaired that week right, I just can't figure out how to get the count for each engine per day and in the cell.

 

Here are the two SQL queries:

To get the row data:

Select
  				engines.Brand,
  				engines.Scale,
  				engines.Description,
  				repairs.raitem.repairDate,
  				COUNT(*) As repairedEngines
		From
  				repairs.raitem Inner Join
  				engines On repairs.raitem.description = engines.engineId
		Where
  				Week(repairs.raitem.repairDate) = Week(Current_Date())
		Group By
  				bli.engines.Brand, bli.engines.Description

 

and

 

To tally the days (I know I can do this with variables in php, but I am using this until I get the cell data.

Select
  					repairs.raitem.repairDate,
  					COUNT(*) as repaired
			From
  					repairs.raitem
			Where
  					YEARweek(repairs.raitem.repairDate) = YEARweek(Now())
			Group By
  					repairs.raitem.repairDate
  				Order By
  					repairs.raitem.repairDate

 

 

[guyfromfl]

Here are the tables:

 

repairs.ra (Return Authorization for repair)

id, customer, etc..

 

repairs.raitem (each returned item on the ra)

id, raID(FK to ra), repairDate, etc.

 

bli.engines (company's product list)

id, description (etc)

[fenway]

And why can't you just group on the DAYOFWEEK()?

[guyfromfl]

When I group by DAYOFWEEK() it only returns !NULL results.

 

If There were no results for tuesday, the chart would say

 

MON TUE WED THUR FRI SAT SUN
1    4   2   3   6    3

 

I guess Im going to have to code a little bit huh...

[fenway]

That's only becuase you're using INNER JOIN, not LEFT JOIN -- has nothing to do with the function.