List:General Discussion« Previous MessageNext Message »
From:Nigel Peck Date:November 18 2011 5:03pm
Subject:Aggregate Query
View as plain text  
Hi all,

Could do with some help please.

I have a query that grabs details of items that have been ordered from 
an ecommerce site. Order details are in "tracking" and ordered items in 
"trackitem".

The query works fine and generates a row for each item, including bits 
of info retrieved from other tables.

So the next thing I need to do is aggregate rows that are for the same 
item, at the same price. I've looked at GROUP BY but can't figure out 
how to get it to group rows with same product name and price, and 
generate a column with the total of the number of aggregated rows in 
each row.

Can someone please point me in the right direction?

My current query is below.

Thanks in advance.

Nigel

--

SELECT
	`tracking`.`tracking_epoch`,
	`trackitem`.`trackitem_itemnumber`,
	`trackitem`.`trackitem_itemname`,
	`trackitem`.`trackitem_prodtotal`,
	`product`.`track_duration`,
	`country`.`country_prs_report_code`,
	`Songs`.`song_composer`,
	`Songs`.`song_publisher`
FROM
	`tracking`

INNER JOIN
	`trackitem`
	ON
		`trackitem`.`trackitem_tracknum`
		=
		`tracking`.`tracking_id`

INNER JOIN
	`product`
	ON
		`product`.`product_number`
		=
		`trackitem`.`trackitem_itemnumber`

INNER JOIN
	`country`
	ON
		`country`.`country_id`
		=
		`tracking`.`tracking_country`

INNER JOIN
	`Songs`
	ON
		`Songs`.`song_id`
		=
		`product`.`song_id`

WHERE
	`tracking`.`tracking_status`
	=
	'C'
AND
	`tracking`.`tracking_epoch`
	>
	1287492451
AND
	`tracking`.`tracking_epoch`
	<
	1304876408
AND
	(
		`country`.`country_id` = '822'
		OR
		`country`.`country_id` = '214'
	)

ORDER BY
	`trackitem`.`trackitem_itemname`

Thread