homepage Welcome to WebmasterWorld Guest from 50.19.169.37
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved