Please help me design this SQL query
Author
Hello. I'm familiar with basic SQL but this is making my head hurt...
I have a table of names and addresses, string fields FORENAME, SURNAME, ADDRESS. I want to select addresses by the number of distinct surnames therein.
For example, If I have stored
(FORENAME,SURNAME,ADDRESS)
John,Smith,A
Jill,Smith,A
Adam,Jones,A
Jack,Smith,B
Jane,Smith,B
I want to retrieve
(ADDRESS,NUM_FAMILIES)
A,2
B,1
Also since this is probably going to involve something like "SELECT ADDRESS, [expression] AS NUM_FAMILIES" is it legal to append "WHERE NUM_FAMILIES>x" even though the NUM_FAMILIES token has only just been defined?
Incidentally, I'm trying to do this in OpenOffice Base, in case that matters.
Thanks if you can help.
You can make use of the aggregate function "count", along with "group by" and a "having" clause:
Count function
Group By
Having
Something like this (untested):
Count function
Group By
Having
Something like this (untested):
select address, count(address) as num_familiesfrom my_address_table_heregroup by addresshaving num_families > 1
Author
Thanks for your suggestion, but that doesn't deal at all the problem! I am aware of COUNT etc, but the only way I can imagine to use them to solve this is imperatively in another language. How, within SQL can I get this result declaratively?
Thanks for your time.
Thanks for your time.
Alimonster's answer is pretty much the best you're going to get without spelling it out for you or describing the entirety of group by/having behavior line by line.
That answer is probably more complete than I would give for something that smells decidedly like homework.
That answer is probably more complete than I would give for something that smells decidedly like homework.
Hint: there exists a distinct keyword you can use in combination with the count function. Then group by what ever you need to distinct.
Author
The closest I've come is
This doesn't work, the inner query doesn't recognise 'a'. A stored procedure might tidy this up but I can't figure out how to make stored procedures work in OpenOffice Base. Any tips?
SELECT DISTINCT address AS a, (SELECT COUNT(DISTINCT surname) AS n FROM Table1 WHERE address=a)FROM Table1This doesn't work, the inner query doesn't recognise 'a'. A stored procedure might tidy this up but I can't figure out how to make stored procedures work in OpenOffice Base. Any tips?
I think this should work (untested):
SELECT DISTINCT address, COUNT(DISTINCT(surname))FROM Table1GROUP BY addressSELECT ans.address, count(*)FROM ( SELECT address, surname, count(*) AS num FROM table GROUP BY address, surname ) AS ansGROUP BY ans.addressA,Smith,2
A,Jones,1
B,Smith,2
And the outer SELECT should return
A,2
B,1
It can probably be refined further (for example, you don't really need the COUNT(*) in the inner select, it's just for demonstration purposes)
Quote:Original post by spraff
Thanks for your suggestion, but that doesn't deal at all the problem!
The reply by Alimonster was exactly what you asked for. It wasnt even pseudocode, it was a 100% valid SQL query that does what you asked for (+ background information).
It seems that you assumed it wouldn't work as-is and just "politely" requested someone else come up with a ready-made query for you.
Quote:Original post by kirsisActually, Alimonster's solution doesn't do what he asked for...Quote:Original post by spraff
Thanks for your suggestion, but that doesn't deal at all the problem!
The reply by Alimonster was exactly what you asked for. It wasnt even pseudocode, it was a 100% valid SQL query that does what you asked for (+ background information).
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
