Jay Fisher

Use HAVING clause with GROUP BY

My HAVING, GROUP BY Statements

  • October 27, 2017 at 10:10 AM
  • Visible to public
/*Query 1 - Show Number of signs for each type of material*/
SELECT  Materials.name as 'Material', SUM(Signs.quantity) as 'Total'
FROM Signs 
INNER JOIN Materials ON Signs.mat_id=Materials.mat_id
GROUP BY Signs.mat_id HAVING COUNT(*)>0;
/*Query 2 - Show Number of Banners That Need Grommets or Power Tabs*/
SELECT CONCAT(Signs.hor_dimens, ' ft x ', Signs.vert_dimens, ' ft') as 'Banners', Finishes.name as 'Finish', SUM(Signs.quantity) as 'Total'
FROM Signs
INNER JOIN Finishes ON Signs.finish_id=Finishes.finish_id
WHERE (Signs.finish_id=1 or Signs.finish_id=2) and (Signs.hor_dimens>4 or Signs.vert_dimens>4) GROUP BY Signs.hor_dimens,Signs.vert_dimens,Signs.finish_id HAVING COUNT(*)>0;