Welcome to WebmasterWorld Guest from 54.221.9.209

Forum Moderators: open

Message Too Old, No Replies

SQL to order records by total number of records

     
1:47 pm on Jul 14, 2010 (gmt 0)

Full Member

10+ Year Member

joined:Mar 8, 2003
posts:234
votes: 0


I have people in one table and I have their items spread over four other tables.. What I want to do is iterate through those people but order the list of them by the total number of each persons records across all four tables... any nice pretty sql statement to do that?
3:55 pm on July 14, 2010 (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:5550
votes: 24


Can you provide some more information about the information you're retrieving? Can the same person appear more than once in another table?
4:44 pm on July 15, 2010 (gmt 0)

Full Member

10+ Year Member

joined:Mar 8, 2003
posts:234
votes: 0


Um, yes..
Within the people table they are unique but within the item type tables, they could have multiple items associated to them..

people
uniqueUSer | uniqueID | details
--------------------------------------
Bob | 2 | from SoCal

item table 1

itemID | itemName | ownerUniqueID
----------------------------------------
12345678 | best thing | 2
32165487 | another thing | 2
12345678 | something | 1
32165487 | another | 4
5:16 pm on July 15, 2010 (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:5550
votes: 24


So you want to
A) count the total number of matching rows across all the linked tables (e.g., 2 for table1 for Bob), or
B) count the number of tables where there are matching rows (e.g., 1 for table1 for Bob)?
8:58 pm on Aug 2, 2010 (gmt 0)

Full Member

10+ Year Member

joined:Mar 8, 2003
posts:234
votes: 0


Choice A:.. I want to say

name has # total(# in each table)
----------------------
Jack has 11 total (5,3,2,1)
Bob has 9 total (1,4,3,2)
Jan has 7 total (3,2,2,0)
Sue has 5 total (2,1,0,2)
9:30 pm on Aug 2, 2010 (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:5550
votes: 24


Ah, then you'll want to try something like:
SELECT people.uniqueID, SUM(a.Items) AS TotalCount
FROM people LEFT OUTER JOIN
(SELECT uniqueID, COUNT(*) AS Items
FROM ItemTable1
GROUP BY uniqueID
UNION
SELECT uniqueID, COUNT(*) AS Items
FROM ItemTable2
GROUP BY uniqueID
UNION
SELECT uniqueID, COUNT(*) AS Items
FROM ItemTable3
GROUP BY uniqueID
UNION
SELECT uniqueID, COUNT(*) AS Items
FROM ItemTable4
GROUP BY uniqueID) a ON people.uniqueID=a.uniqueID
GROUP BY people.uniqueID
ORDER BY TotalCount DESC

You'll probably need to do some tweaking- you may not get exactly what you're looking for if you have any NULL values.
10:07 pm on Aug 2, 2010 (gmt 0)

Full Member

10+ Year Member

joined:Mar 8, 2003
posts:234
votes: 0


wow. I have no words.