VbzCart/queries/qryCat Titles Item stats

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 01:36, 13 April 2009 by Woozle (talk | contribs) (4/12 update; more doc)
Jump to navigation Jump to search

Details

  • Requires:
  1. REDIRECT Template:l/vc/query,
  2. REDIRECT Template:l/vc/table, qryCat_Depts
  • History:
    • 2009-04-12 updated to use qryCat_Items_Stock instead of depending on deprecated cat_items.qtyInStock field
  • Note: This query replaces v_titles and is used by qryCat_Titles_web.

SQL

<mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_stats AS SELECT 

 ID_Title,
 ID_Dept,
 DateAdded AS WhenAdded,
 SUM(IF(i.isForSale,1,0)) AS cntForSale,
 SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 SUM(i.qtyForSale) AS qtyForSale,
 MIN(i.PriceSell) AS currMinSell,
 MAX(i.PriceSell) AS currMaxSell,
 d.ID_Supplier,
 UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
 LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
 t.Name

FROM (qryCat_Items_Stock AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN qryCat_Depts AS d ON t.ID_dept=d.ID GROUP BY i.ID_Title;</mysql>

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