Welcome to WebmasterWorld Guest from 54.147.20.131

Forum Moderators: open

Message Too Old, No Replies

SQL to order records by total number of records

     

The_Hat

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

10+ Year Member



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

3:55 pm on Jul 14, 2010 (gmt 0)

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



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

4:44 pm on Jul 15, 2010 (gmt 0)

10+ Year Member



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

5:16 pm on Jul 15, 2010 (gmt 0)

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



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

8:58 pm on Aug 2, 2010 (gmt 0)

10+ Year Member



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

9:30 pm on Aug 2, 2010 (gmt 0)

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



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

10:07 pm on Aug 2, 2010 (gmt 0)

10+ Year Member



wow. I have no words.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month