VbzCart/queries/qryItTypsDepts grpItems
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
About
- Requires:
- REDIRECT Template:l/vc/query,
- 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_remainingqryCat_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
- 2009-04-12 No longer caching stock-quantity fields in cat_items, so had to pull in
- 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>