Difference between revisions of "VbzCart/queries/qryTitles Item info"
Jump to navigation
Jump to search
(Created page with '==About== * '''Purpose''': Lists titles with some additional item-oriented info * '''Used by''': titles AdminList() (replaces {{vbzcart/query|qryCat_Titles_Item_stats}} * '''Hist…') |
(renamed count field for consistency) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Purpose''': Lists titles with some additional item-oriented info | * '''Purpose''': Lists titles with some additional item-oriented info | ||
| − | * '''Used by''': titles AdminList() (replaces {{vbzcart/query|qryCat_Titles_Item_stats}} | + | * '''Requires''': {{vbzcart|table|cat_items}}, {{vbzcart/query|qryStk_items_remaining}} |
| + | * '''Used by''': titles AdminList() (replaces {{vbzcart/query|qryCat_Titles_Item_stats}}) | ||
* '''History''': | * '''History''': | ||
** '''2010-11-07''' Created for titles AdminList(): {{vbzcart/query|qryCat_Titles_Item_stats}} was not showing titles that didn't have any Item records | ** '''2010-11-07''' Created for titles AdminList(): {{vbzcart/query|qryCat_Titles_Item_stats}} was not showing titles that didn't have any Item records | ||
| Line 8: | Line 9: | ||
SELECT | SELECT | ||
t.*, | t.*, | ||
| − | COUNT(i.ID) AS | + | COUNT(i.ID) AS cntItems, |
| − | SUM(s.QtyForSale) AS | + | SUM(s.QtyForSale) AS qtyForSale, |
| + | SUM(i.isForSale) AS cntForSale, | ||
| + | SUM(i.isInPrint) AS cntInPrint, | ||
| + | MIN(i.PriceSell) AS dlrMinSell, | ||
| + | MAX(i.PriceSell) AS dlrMaxSell | ||
FROM (cat_titles AS t | FROM (cat_titles AS t | ||
LEFT JOIN cat_items AS i ON i.ID_Title=t.ID) | LEFT JOIN cat_items AS i ON i.ID_Title=t.ID) | ||
Latest revision as of 19:55, 7 November 2010
About
- Purpose: Lists titles with some additional item-oriented info
- Requires: cat_items,
- REDIRECT Template:l/vc/query
- Used by: titles AdminList() (replaces qryCat_Titles_Item_stats)
- History:
- 2010-11-07 Created for titles AdminList(): qryCat_Titles_Item_stats was not showing titles that didn't have any Item records
SQL
<mysql>CREATE OR REPLACE VIEW qryTitles_Item_info AS SELECT
t.*, COUNT(i.ID) AS cntItems, SUM(s.QtyForSale) AS qtyForSale, SUM(i.isForSale) AS cntForSale, SUM(i.isInPrint) AS cntInPrint, MIN(i.PriceSell) AS dlrMinSell, MAX(i.PriceSell) AS dlrMaxSell
FROM (cat_titles AS t
LEFT JOIN cat_items AS i ON i.ID_Title=t.ID) LEFT JOIN qryStk_items_remaining AS s ON i.ID=s.ID_Item
GROUP BY t.ID; </mysql>