Calculate COUNT as a field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Calculate COUNT as a field

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Calculate COUNT as a field

Post 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?
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Calculate COUNT as a field

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Calculate COUNT as a field

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Calculate COUNT as a field

Post 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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Calculate COUNT as a field

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Calculate COUNT as a field

Post 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.
Post Reply