Welcome to WebmasterWorld Guest from 54.159.94.253

Forum Moderators: open

Message Too Old, No Replies

Merging 2 queries, using LEFT JOIN and GROUP BY

     
8:55 am on Jun 8, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 707
votes: 58


I have two queries, and I'm trying to merge them into one:

// Returns something like:
// 10 | Cars
// 20 | Trucks
// 30 | ATVs
SELECT sorter, subcat FROM classifieds_cats
WHERE category='Autos'
ORDER BY sorter, subcat

// Returns something like:
// Cars | 120
// Trucks | 113
// ATVs | 48
SELECT subcat, COUNT(subcat) AS nums
FROM classifieds
WHERE category = 'Autos'
GROUP BY subcat

The second query just returns a number and a subcat that matches that in the first query. But I'm stumbling on how to get a count for each subcat.

Here's what I thought would work:

SELECT
classifieds_cats.sorter,
classifieds_cats.subcat,
COUNT(classifieds.subcat) AS nums
FROM classifieds_cats
LEFT JOIN classifieds
ON classifieds.category = classifieds_cats.category
WHERE classifieds_cats.category = 'Autos'
GROUP BY classifieds_cats.subcat
ORDER BY sorter, subcat

(I tried to remove any unnecessary parts of the otherwise long query, so please forgive any typos)

I thought this would return:

// 10 | Cars | 120
// 20 | Trucks | 113
// 30 | ATVs | 48

But instead it sets "nums" to a total count of all items matching subcat, so I end up with:

// 10 | Cars | 281
// 20 | Trucks | 281
// 30 | ATVs | 281

So how do I get the query to count each subcat independently?
6:32 pm on June 8, 2017 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5753
votes: 120


Off the cuff, try:
SELECT classifieds_cats.sorter, classifieds_cats.subcat, COUNT(classifieds.subcat) AS nums
FROM classifieds_cats
LEFT JOIN classifieds ON classifieds.category = classifieds_cats.category
WHERE classifieds_cats.category = 'Autos'
GROUP BY classifieds_cats.sorter, classifieds_cats.subcat
ORDER BY sorter, subcat
6:38 pm on June 8, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 707
votes: 58


I did, but same result... just a total count instead of a count on each subcat :-(
11:10 pm on June 8, 2017 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5753
votes: 120


What about:
SELECT classifieds_cats.sorter, classifieds.subcat, COUNT(classifieds.subcat) AS nums
FROM classifieds
LEFT JOIN classifieds_cats ON classifieds.category = classifieds_cats.category
WHERE classifieds.category = 'Autos'
GROUP BY classifieds_cats.sorter, classifieds.subcat
ORDER BY sorter, subcat
12:24 am on June 9, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 707
votes: 58


We're getting close! That almost works, LIA, but for some reason I'm getting:

// 10 | Cars | 120
// 10 | Trucks | 113
// 10 | ATVs | 48
// 20 | Cars | 120
// 20 | Trucks | 113
// 20 | ATVs | 48
// 30 | Cars | 120
// 30 | Trucks | 113
// 30 | ATVs | 48

And the numbers don't match up with what I have before, either. For example, in the live code, today has 129 cars and 108 trucks, but this query shows 162 cars and 141 trucks. That difference of 33 isn't consistent, though; eg, I have 3 golf carts, but the query shows 6.

I'm poking around to see if I can find the right combination to fix that, and I'll post back if I find it. But if you see this before I reply and realize what modification I need to make, please post back and let me know :-D
3:53 pm on June 9, 2017 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5753
votes: 120


Try changing to an INNER JOIN instead of LEFT JOIN.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members