Aggregate Query
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` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Totalling Counts done in Subqueries
Hi all, I'm hoping someone can help me with this please. Is there a way to total counts done in subqueries? So I want to do: -=-=-=-=-=-=-= SELECT `Notes`.`note_id`, `Notes`.`last_updated_datetime`, `Notes`.`event_date`, `Notes`.`subject`, `Notes`.`summary`, `Notes`.`content`, (SELECT COUNT(*) FROM `Notes__Issues` WHERE `Notes`.`note_id` = `Notes__Issues`.`note_id` ) AS 'linked_issues_count', (SELECT COUNT(*) FROM `Notes__People` WHERE `Notes`.`note_id` = `Notes__People`.`note_id` ) AS 'linked_people_count', (SELECT COUNT(*) FROM `Notes__Organisations` WHERE `Notes`.`note_id` = `Notes__Organisations`.`note_id` ) AS 'linked_organisations_count', (linked_issues_count + linked_people_count + linked_organisations_count) AS 'total' FROM `Notes` WHERE ( `added_user_id` = '6' ) ORDER BY last_updated_datetime DESC LIMIT 25 -=-=-=-=-=-=-=-=- Note the: (linked_issues_count + linked_people_count + linked_organisations_count) AS 'total' That's the bit that doesn't work. Is there a way? Thanks Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Totalling Counts done in Subqueries
Thanks Nigel and Peter, I went for Nigel's solution below. Both very useful, learnt a lot, thank you. Cheers, Nigel nigel wood wrote: Is there a way to total counts done in subqueries? Never done this but my educated guess is: SELECT `Notes`.`note_id`, `Notes`.`last_updated_datetime`, `Notes`.`event_date`, `Notes`.`subject`, `Notes`.`summary`, `Notes`.`content`, @linked_issues_count := (SELECT COUNT(*) FROM `Notes__Issues` WHERE `Notes`.`note_id` = `Notes__Issues`.`note_id` ) AS 'linked_issues_count', @linked_people_count := (SELECT COUNT(*) FROM `Notes__People` WHERE `Notes`.`note_id` = `Notes__People`.`note_id` ) AS 'linked_people_count', @linked_organisations_count :=( SELECT COUNT(*) FROM `Notes__Organisations` WHERE `Notes`.`note_id` = `Notes__Organisations`.`note_id` ) AS 'linked_organisations_count', (@linked_issues_count + @linked_people_count + @linked_organisations_count) AS 'total' FROM `Notes` WHERE ( `added_user_id` = '6' ) ORDER BY last_updated_datetime DESC LIMIT 25 hope that helps Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SELECT of records that have a matching record in a many to many table
Can someone please help me with this one? I'm trying to SELECT from a table only those records that have a record, matching a search term, in a table related by a many to many relationship. The many to many relationship is in a mapping/junction table. Here's an example of what I have so far: -=-=-=-=-=-=-=-=-=-=-=-=- SELECT `Notes`.`note_id` FROM `Notes` INNER JOIN `Notes__Districts` ON `Notes__Districts`.`note_id` = `Notes`.`note_id` LEFT JOIN `Districts` ON `Districts`.`district_id` = `Notes__Districts`.`district_id` WHERE `Districts`.`name` REGEXP 'bradford'; -=-=-=-=-=-=-=-=-=-=-=-=- Hopefully someone can see what I'm trying to do here and point me in the right direction :) Maybe I need to use a subquery? I've got a feeling I can do this without that but can't get my head round how to set up the JOINs in this case with having to use three tables in the one query, I'm only used to two tables at once. I couldn't find any tutorials that cover this. Thanks in advance, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two COUNTs in one query
Thanks to everyone who helped me out with this, just what I needed and this is now working for me. One further question... I'm using a subquery as suggested: SELECT `Organisations`.`organisation_id`, `Organisations`.`name`, (SELECT COUNT(*) FROM `Notes__Organisations` WHERE `Notes__Organisations`.`organisation_id` = `Organisations`.`organisation_id`) AS 'linked_notes_count', (SELECT COUNT(*) FROM `Organisations__People` WHERE `Organisations__People`.`organisation_id` = `Organisations`.`organisation_id`) AS 'linked_people_count' FROM `Organisations` Is there a way to total the counts? So I want to do: -=-=-=-=-=-=-= SELECT `Notes`.`note_id`, `Notes`.`last_updated_datetime`, `Notes`.`event_date`, `Notes`.`subject`, `Notes`.`summary`, `Notes`.`content`, (SELECT COUNT(*) FROM `Notes__Issues` WHERE `Notes`.`note_id` = `Notes__Issues`.`note_id` ) AS 'linked_issues_count', (SELECT COUNT(*) FROM `Notes__People` WHERE `Notes`.`note_id` = `Notes__People`.`note_id` ) AS 'linked_people_count', (SELECT COUNT(*) FROM `Notes__Organisations` WHERE `Notes`.`note_id` = `Notes__Organisations`.`note_id` ) AS 'linked_organisations_count', (linked_issues_count + linked_people_count + linked_organisations_count) AS 'total' FROM `Notes` WHERE ( `added_user_id` = '6' ) ORDER BY last_updated_datetime DESC LIMIT 25 -=-=-=-=-=-=-=-=- Notes the (linked_issues_count + linked_people_count + linked_organisations_count) AS 'total'. The above does not work, is there a way? Thanks Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Two COUNTs in one query
I think I probably can't do what I want, but am hoping I'm wrong. Please help :) I have three tables: Organisations - organisation_id - name - - 1 - Org A - - 2 - Org B - Notes__Organisations - - organisation_id - note_id - - - 1 - 10 - - 1 - 11 - - Organisations__People --- - organisation_id - person_id - --- - 1 - 20- - 1 - 21- - 1 - 22- - 1 - 23- - 2 - 24- --- Organisations has one-to-many relationships with Notes__Organisations and Organisations__People. I want to select the name and id from Organisations along with a count of the number of one-to-many relationships it has in each of the two tables. I want a count of 0 if there are none, so I'm using two LEFT JOINs and a GROUP BY. I want to do this in a single query (so I can limit the set and sort it, displaying the limited set in my interface). I have: = SELECT `Organisations`.`organisation_id`, `Organisations`.`name`, COUNT(`Notes__Organisations`.`organisation_id`) AS 'linked_notes_count', COUNT(`Organisations__People`.`organisation_id`) AS 'linked_people_count' FROM `Organisations` LEFT JOIN `Notes__Organisations` ON `Organisations`.`organisation_id` = `Notes__Organisations`.`organisation_id` LEFT JOIN `Organisations__People` ON `Organisations`.`organisation_id` = `Organisations__People`.`organisation_id` GROUP BY `Organisations`.`organisation_id` ORDER BY name LIMIT 50, 25 = Obviously the LIMIT does not apply to this example data, but I wanted to show my whole query. So with the above data I want: --- - organisation_id - name - linked_notes_count - linked_people_count - --- - 1 - Org A - 2 - 4 - - 2 - Org B - 0 - 1 - --- What I actually get is unpredictable. Something like: --- - organisation_id - name - linked_notes_count - linked_people_count - --- - 1 - Org A - 4 - 4 - - 2 - Org B - 0 - 1 - --- But it varies and there's no pattern. Is there a way? Should I use a stored procedure instead to do the counts? (not used them yet - don't even know if that's a valid suggestion). Thanks in advance, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting single results per (left) record with INNER JOIN
Arthur Fuller wrote: Won't a simple LIMIT 1 do what you want? Or am I missing something? Thanks for getting back to me. You're missing something, but I probably didn't explain very well. There can be multiple matches from the People table, but when more than one address matches, I get more than one result per record in the People table (one per matching address). Hopefully this will explain better: - People Table - person_id | name| -=-=-=-=-=-=-=-=-=-=-= 1 | Nigel | 2 | Lindsey | -=-=-=-=-=-=-=-=-=-=-= - Person_postal_addresses Table - person_id | address | -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 1 | example road... | 1 | example street... | 2 | example lane... | -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= (person_id is FK of person_id in People table) If I'm searching for example, I would currently get a result set of: 1 Nigel 1 Nigel 2 Lindsey (One result per matching address) I want the result set to be: 1 Nigel 2 Lindsey (One result per People row that has a matching address) I could go through my result set and turn it into this, but I would rather do it in the query as I'm sure that that will be more efficient. Cheers, Nigel On Fri, Mar 13, 2009 at 3:24 PM, Nigel Peck nigel.p...@miswebdesign.comwrote: Nigel Peck wrote: SELECT `People`.`person_id`, `People`.`name` FROM `People` INNER JOIN `Person_postal_addresses` ON `Person_postal_addresses`.`person_id` = `People`.`person_id` WHERE `People`.`name` REGEXP 'example' OR `Person_postal_addresses`.`address` REGEXP 'example' ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting single results per (left) record with INNER JOIN
Johan De Meersman wrote: Either HAVING, or an additional GROUP BY field of person_postal_address.person_id should do, I think. That's great, thanks, just what I needed :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Getting single results per (left) record with INNER JOIN
I'm hoping someone can point me in the right direction for what I need, to save me trawling through books and Google when I don't know what I'm looking for. I'm using an INNER JOIN to query a table that has a one-to-many relationship with the table in my FROM clause, but I only want one result in the result set per record in the table in my FROM clause, rather than multiple results if there are multiple matching records in the JOINed table. I'm just not sure how to do that (or rather can't remember!). My query is: SELECT `People`.`person_id`, `People`.`name`, FROM `People` INNER JOIN `Person_postal_addresses` ON `Person_postal_addresses`.`person_id` = `People`.`person_id` WHERE `People`.`name` REGEXP 'example' OR `Person_postal_addresses`.`address` REGEXP 'example' ; How do I alter that to get one result per matching row in the People table? When there are multiple matches, for one row in People, for: `Person_postal_addresses`.`address` REGEXP '1' Thanks in advance, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting single results per (left) record with INNER JOIN
Nigel Peck wrote: ... My query is: SELECT `People`.`person_id`, `People`.`name`, FROM `People` INNER JOIN `Person_postal_addresses` ON `Person_postal_addresses`.`person_id` = `People`.`person_id` WHERE `People`.`name` REGEXP 'example' OR `Person_postal_addresses`.`address` REGEXP 'example' ; Sorry, there was a typo in my query, should have been: SELECT `People`.`person_id`, `People`.`name` FROM `People` INNER JOIN `Person_postal_addresses` ON `Person_postal_addresses`.`person_id` = `People`.`person_id` WHERE `People`.`name` REGEXP 'example' OR `Person_postal_addresses`.`address` REGEXP 'example' ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
JOIN - Which and how?
Hi all, I'm having trouble figuring out how to construct a query and am hoping someone can help... I have three tables: Raw_materials +++---+ | raw_mat_id | name | count | +++---+ | 1 | Small Organza Bag |10 | | 2 | Lavender Incense Cones |10 | +++---+ Raw_materials__Products +++ | raw_mat_id | product_id | +++ | 1 | 2 | | 2 | 2 | | 2 | 1 | +++ Products ++-+---+---+ | product_id | name| price | rrp | ++-+---+---+ | 1 | Large Salt Lamp | 59.99 | 69.99 | | 2 | Lavender Incense Cones (15) | 3.99 | 0.00 | ++-+---+---+ CREATE TABLE `Raw_materials__Products` ( `raw_mat_id` int(10) unsigned NOT NULL, `product_id` int(10) unsigned NOT NULL, PRIMARY KEY (`raw_mat_id`,`product_id`), KEY `product_id` (`product_id`), CONSTRAINT `Raw_materials__Products:raw_mat_id` FOREIGN KEY (`raw_mat_id`) REFERENCES `Raw_materials` (`raw_mat_id`) ON UPDATE CASCADE, CONSTRAINT `Raw_materials__Products:product_id` FOREIGN KEY (`product_id`) REFERENCES `Products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | Knowing a product_id, I want to select all of the Raw_materials rows that do NOT already have a relationship in Raw_materials__Products. So for product_id 1 I would get raw_mat_id 1 and for product_id 2 I would get an empty result set. I tried various queries but can't get anywhere, please help. Thanks in advance, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN - Which and how?
Ananda Kumar wrote: did u try this select * from raw_materials where raw_mat_id not in (select raw_mat_id from raw_materials__Products); Hi Ananda, Thanks for this, I'm really looking to do this in a single SELECT, for efficiency. I'm sure it's possible with a join but just not sure how. I've just tried: SELECT `Raw__materials`.`raw_mat_id`, `Raw__materials`.`name` FROM `Raw__materials` LEFT JOIN `Raw__materials__Products` ON `Raw__materials`.`raw_mat_id` = `Raw__materials__Products`.`raw_mat_id` WHERE `Raw__materials__Products`.`stock_id` IS NULL AND `Raw__materials__Products`.`product_id` = '1' But I just get an empty result set. Although the manual seems to suggest that it should work. I tried taking out AND `Raw__materials__Products`.`product_id` = '1' but still an empty result set. Cheers, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN - Which and how?
Nigel Peck wrote: Ananda Kumar wrote: did u try this select * from raw_materials where raw_mat_id not in (select raw_mat_id from raw_materials__Products); Hi Ananda, Thanks for this, I'm really looking to do this in a single SELECT, for efficiency. I'm sure it's possible with a join but just not sure how. I've just tried: SELECT `Raw__materials`.`raw_mat_id`, `Raw__materials`.`name` FROM `Raw__materials` LEFT JOIN `Raw__materials__Products` ON `Raw__materials`.`raw_mat_id` = `Raw__materials__Products`.`raw_mat_id` WHERE `Raw__materials__Products`.`stock_id` IS NULL AND `Raw__materials__Products`.`product_id` = '1' But I just get an empty result set. Although the manual seems to suggest that it should work. I tried taking out AND `Raw__materials__Products`.`product_id` = '1' but still an empty result set. Cheers, Nigel Sorry, make that: SELECT `Raw_materials`.`raw_mat_id`, `Raw_materials`.`name` FROM `Raw_materials` LEFT JOIN `Raw_materials__Products` ON `Raw_materials`.`raw_mat_id` = `Raw_materials__Products`.`raw_mat_id` WHERE `Raw_materials__Products`.`raw_mat_id` IS NULL AND `Raw_materials__Products`.`product_id` = '1' Cheers, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN - Which and how?
Nigel Peck wrote: Sorry, make that: SELECT `Raw_materials`.`raw_mat_id`, `Raw_materials`.`name` FROM `Raw_materials` LEFT JOIN `Raw_materials__Products` ON `Raw_materials`.`raw_mat_id` = `Raw_materials__Products`.`raw_mat_id` WHERE `Raw_materials__Products`.`raw_mat_id` IS NULL AND `Raw_materials__Products`.`product_id` = '1' I got it, I needed to move checking the product_id to the ON clause: SELECT `Raw_materials`.`raw_mat_id`, `Raw_materials`.`name` FROM `Raw_materials` LEFT JOIN `Raw_materials__Products` ON `Raw_materials`.`raw_mat_id` = `Raw_materials__Products`.`raw_mat_id` AND `Raw_materials__Products`.`product_id` = '1' WHERE `Raw_materials__Products`.`raw_mat_id` IS NULL Cheers, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
