VbzCart/queries/qryItTypsDepts grpItems

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | queries
Revision as of 10:50, 13 April 2009 by Woozle (talk | contribs) (note about similarity to other query)
Jump to navigation Jump to search

About

  • Requires:
  1. REDIRECT Template:l/vc/query,
  2. REDIRECT Template:l/vc/table
  • 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
  • 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,

 SUM(IF(i.isForSale,1,0)) AS cntForSale,
 SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 /* SUM(IF(i.QtyForSale>0,1,0)) AS cntStkForSale, */
 SUM(i.qtyForSale) AS qtyForSale

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

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/qryItTypsDepts_grpItems&oldid=11644"