| SQL to order records by total number of records
|
The_Hat
msg:4169971 | 1:47 pm on Jul 14, 2010 (gmt 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?
|
LifeinAsia
msg:4170072 | 3:55 pm on Jul 14, 2010 (gmt 0) |
Can you provide some more information about the information you're retrieving? Can the same person appear more than once in another table?
|
The_Hat
msg:4170857 | 4:44 pm on Jul 15, 2010 (gmt 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
|
LifeinAsia
msg:4170883 | 5:16 pm on Jul 15, 2010 (gmt 0) |
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)?
|
The_Hat
msg:4180801 | 8:58 pm on Aug 2, 2010 (gmt 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)
|
LifeinAsia
msg:4180806 | 9:30 pm on Aug 2, 2010 (gmt 0) |
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.
|
The_Hat
msg:4180814 | 10:07 pm on Aug 2, 2010 (gmt 0) |
wow. I have no words.
|
|
|
