Display distinct count max number of favourites based on categoryId

Question

0.00/5 (No votes)

See more:

i have 5 tables category ,business ,metro ,user,favourites

Category

id | category_name
    -------------------
    1  | bar
    2  | cafe
    3  | lunch
    4  | dinner

Business
    id | business_name | zip        | address            |metro_id
    --------------------------------------------------------------
    1  | sanders      | 023232      | abc1               | 1
    2  | ipc          | 023232      | abc2               | 1
    3  | mircleFoods  | 023232      | abc3               | 1
    4  | salt         | 023232      | abc4               | 2

Metro

id | name
    -------------------
    1  | boston
    2  | newYork

Favourites

id | user_id | business_id | metro_id | category_id
    ----------------------------------------------------
    1  | 1       | 1           | 3         | 1
    2  | 1       | 1           | 4         | 2
    3  | 1       | 1           | 3         | 1
    4  | 1       | 1           | 5         | 2

User

id | firstName | LastName |userName   | Password
    ----------------------------------------------------
    1  | john   | mark   | jmark1       |123
    2  | john  | mark   | jmark2        | 123
    3  | john   | mark  | jmark3        | 123
    4  | john   | mark  | jmark4        | 123

i have been trying to select a category with the highest count of favs in a particular city and display the business name which has its category favouried max.

i have been hoping to get result e.g

RESULT 1

{
    Metro->boston
    category->bar
    businessName->sanders
    favs->23}

RESULT 2

{
    Metro->boston
    category->cafe
    businessName->sanders
    favs->333}

i have tried many different queries but now have started trying to implement it by breaking it up here is some of the queries i have tried

1.

SQL

SELECT c.category_name,b.business_name,COUNT(f.user_id) FROM business b, category c, favourites f
    INNER JOIN business ON business.id=favourites.business_id
    INNER JOIN category ON category.id=favourites.category_id
    GROUP BY c.category_name,b.business_name

2.

SQL

SELECT c.category_name,(SELECT COUNT(*) FROM favourites) AS fs,b.business_name FROM(favourites f,business b,category c) GROUP BY c.category_name LIMIT 0,1

3.

SQL

SELECT f.category_id, COUNT(f.user_id) AS f FROM (favourites f,metro m)  GROUP BY category_id HAVING MAX(f.user_id)=(SELECT COUNT(user_id) FROM favourites)

Please kindly guide me , i am having trouble proceeding with this .Thankyou

Posted 28-Apr-13 21:22pm

mubarakahmad

Updated 28-Apr-13 23:12pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-04-28T21:34:00

If you want to get MAX of id for each category_id, try this:

SQL

SELECT category_id, MAX(id)
FROM Favourites
GROUP BY category_id

If you want to count id for each category_id, try this:

SQL

SELECT category_id, COUNT(id)
FROM Favourites
GROUP BY category_id

or use

SQL

SELECT category_id, COUNT(DISTINCT id)
FROM Favourites
GROUP BY category_id

to get distinct id's.

More: MySQL aggregate functions[^]

[EDIT #2]

SQL

SELECT c.category_name, b.business_name, COUNT(f.user_id)
FROM favourites f LEFT JOIN business b ON f.business_id=b.id LEFT JOIN category c ON c.id=f.category_id
GROUP BY c.category_name, b.business_name

More about JOIN's[^], Visual Representation of SQL Joins[^].
[/EDIT]

Display distinct count max number of favourites based on categoryId