I have a sql query that pulls data on manufacturing defects. I am using GROUP BY WITH ROLLUP to provide a summary of defects by part number. I include two columns in my GROUP BY for part number and defect name. The problem is that I want to sort the number of defects so the highest quantity is on top. I understand that with MYSQL WITH ROLLUP AND ORDER BY are mutually exclusive, but I can use ASC and DESC. This would be fine except the defect count column is not one of the things I wan to group by.

$mysqldb->query("SELECT Depts.DeptName AS Dept,
		 				Production.partno AS Part,
		 				Partno.PartDesc AS Description,
		 				Defects_All.DefectName AS Type,
		 				SUM(DefectsLog.DefectCount) AS Defects
				FROM (((Production LEFT JOIN DefectsLog ON Production.LogNo = DefectsLog.ScrapLogID)
						LEFT JOIN Defects_All ON DefectsLog.DefectId = Defects_All.Defcode)
						LEFT JOIN Partno ON Production.partno = Partno.partno)
						LEFT JOIN Depts ON Production.Department = Depts.DeptNo
				WHERE DefectsLog.DefectCount<>0 $deptString
					AND Production.ProdDate >=$startDate
					AND Production.ProdDate <=$endDate
				GROUP BY Production.partno, Defects_All.DefectName WITH ROLLUP");
    14 days later

    My question would be do you need to use the "WITH ROLLUP" clause to show the NULL values?

    If it is required, then create a view of your query then query the view with the ORDER BY added.

    -- within MySQL
CREATE VIEW viewDefects AS
  SELECT Depts. DeptName, ......

-- within PHP
$mysqldb->query("SELECT * FROM viewDefects ORDER BY Defects DESC");

      My question would be do you need to use the "WITH ROLLUP" clause to show the NULL values?

      If it is required, then create a view of your query then query the view with the ORDER BY added.

      -- within MySQL
CREATE VIEW viewDefects AS
  SELECT Depts. DeptName, ......

-- within PHP
$mysqldb->query("SELECT * FROM viewDefects ORDER BY Defects DESC");
        Write a Reply...