VbzCart/queries/qryItTypsDepts grpItems

About

• Requires:

1. REDIRECT Template:l/vc/query,
2. REDIRECT Template:l/vc/table, cat_depts

• Used by: qryItTypsDepts_ItTyps
• History:
  • 2009-04-12 No longer caching stock-quantity fields in cat_items, so had to pull in v_stk_items_remaining qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for
  • 2009-11-29 Adding ID_Supplier for more efficient coding (see qryItTypsDepts_ItTyps)
    • Also removing commented-out cntStkForSale
• Notes:
  • This is essentially the same query as qryTitles_ItTyps_grpItems but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp).

SQL

<mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS SELECT

 i.ID_ItTyp, t.ID_Dept, d.ID_Supplier,

 SUM(IF(i.isForSale,1,0)) AS cntForSale,
 SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 SUM(i.qtyForSale) AS qtyForSale

FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN cat_depts AS d ON t.ID_Dept=d.ID GROUP BY i.ID_ItTyp, t.ID_Dept HAVING cntForSale;</mysql>