Page 1 of 1
Calculate COUNT as a field
Posted: Sat Mar 22, 2008 12:00 am
by alex.barylski
Do I need to use a sub-select???
How do I do this?
Code: Select all
SELECT *, count = (SELECT COUNT(*) FROM another_table) FROM table1
Ideally I want to be able to have people sort on this quasi-field...
Is this possible?
Re: Calculate COUNT as a field
Posted: Sat Mar 22, 2008 12:15 am
by John Cartwright
Code: Select all
SELECT id, (SELECT COUNT(*) FROM table2 WHERE .....) AS `count` FROM table1
I believe..
How is your subquery related to table1? Is a subquery necessary?
Re: Calculate COUNT as a field
Posted: Sat Mar 22, 2008 1:16 am
by alex.barylski
Oh wow...so I was almost right on the money.
One table is for a list of products, the sub-query is for the customers who have purchased products. I basically want to display the products in a table and show how many customers have purchased each product -- thus the quasi-field.
I could use an actual field and just update it accordingly, but that would require a fare amount of retro-fitting my code and the list isn't that popular so...I figure I'll just calculate the field count dynamically and be done with it.
Re: Calculate COUNT as a field
Posted: Sat Mar 22, 2008 1:38 am
by alex.barylski
Just realized as I try and get this query working. The sub-query depends on the PKID of each record queries in the primary query -- how do I get that information into the sub-query? :S
Re: Calculate COUNT as a field
Posted: Sat Mar 22, 2008 2:06 am
by John Cartwright
You can reference the columns you wish to join in the WHERE clause just like you would any join. However, as I suspected there is no need for a subquery at all..
(untested)
Code: Select all
SELECT id, productname, COUNT(products_orders.id) AS `purchased` FROM products
LEFT JOIN products_orders ON products_orders.product_id = products.id
GROUP BY products.id
should return something like
Re: Calculate COUNT as a field
Posted: Sat Mar 22, 2008 1:54 pm
by alex.barylski
EDIT: Even when the purchases for the product are zero -- the COUNT still returns 1 -- any ideas??
Beauty -- thank man.
However I wonder why the need for the explicit LEFT JOIN -- isn't this already implied?
Re: Calculate COUNT as a field
Posted: Mon Mar 24, 2008 9:00 am
by John Cartwright
Round #2
Code: Select all
SELECT id, productname, IF(products_orders.id <> NULL, COUNT(products_orders.id), 0) AS `purchased` FROM products
LEFT JOIN products_orders ON products_orders.product_id = products.id
GROUP BY products.id
I can't remember by default whether a JOIN is a LEFT or INNER, so I always explicitly state it.