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

Code: Select all

1   foo   5
2   bar   2
3   fee   0
4   foo   4

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.