Difference between revisions of "VbzCart/queries/qryItTypsDepts grpItems"
Jump to navigation
Jump to search
(using sub-query instead of doing the JOIN right here) |
(note about similarity to other query) |
||
| Line 4: | Line 4: | ||
* '''History''': | * '''History''': | ||
** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for | ** '''2009-04-12''' No longer caching stock-quantity fields in cat_items, so had to pull in <s>v_stk_items_remaining</s> qryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for | ||
| + | * '''Notes''': | ||
| + | ** This is essentially the same query as {{vbzcart/query|qryTitles_ItTyps_grpItems}} but grouped by ID_Dept (and ID_ItTyp) instead of ID_Title (and ID_ItTyp). | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS | ||
Revision as of 10:50, 13 April 2009
About
- Requires:
- REDIRECT Template:l/vc/query,
- 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_remainingqryCat_Items_Stock; removing cntStkForSale until I find out what it is needed for
- 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, 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>