VbzCart/docs/queries/qryCat Titles Item stats
Jump to navigation
Jump to search
Details
- Requires: Template:Vbzcart/query, Template:Vbzcart/table, Template:Vbzcart/query
- 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
<section begin=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> <section end=sql />