VbzCart/queries/qryTitles Item info - Revision history

Woozle: renamed count field for consistency

2010-11-07T19:55:28Z

renamed count field for consistency

← Older revision		Revision as of 19:55, 7 November 2010
Line 9:		Line 9:
	SELECT		SELECT
	t.*,		t.*,
	COUNT(i.ID) AS ~~Count_Items~~,		COUNT(i.ID) AS cntItems,
	SUM(s.QtyForSale) AS qtyForSale,		SUM(s.QtyForSale) AS qtyForSale,
	SUM(i.isForSale) AS cntForSale,		SUM(i.isForSale) AS cntForSale,

Woozle: some more fields

2010-11-07T19:51:21Z

some more fields

← Older revision		Revision as of 19:51, 7 November 2010
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 9:		Line 10:
	t.*,		t.*,
	COUNT(i.ID) AS Count_Items,		COUNT(i.ID) AS Count_Items,
	SUM(s.QtyForSale) AS ~~QtyForSale~~		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)

Woozle: 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…'

2010-11-07T19:19:46Z

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…'

New page

==About==
* '''Purpose''': Lists titles with some additional item-oriented info
* '''Used by''': titles AdminList() (replaces {{vbzcart/query|qryCat_Titles_Item_stats}}
* '''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
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryTitles_Item_info AS
SELECT
t.*,
COUNT(i.ID) AS Count_Items,
SUM(s.QtyForSale) AS QtyForSale
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>
<section end=sql />