-1

I am working on past national censuses stored in an Oracle database. My main tools for working with it, are MS Access and LibreOffice Base, depending on what kind of task I have to solve. I do not have direct access to the dbase; I cannot, for instance, run update queries directly on the main tables, but I can do this on subtables I have created in my environment.

I would like to list all unique standardised names from a census, with the number of instances shown as a count, and listing all variants of the name in a seperate column. How would such a query be written?

In the example below, the …S following Firstname, indicates which standard name the source’s first name is encoded under.

Firstname FirstnameS
Tor       Tor
Thor      Tor
Per       Per
Peer      Per
Pær       Per
Pär       Per
Caroline  Karoline
Charoline Karoline
Karoliine Karoline

Desired output

FirstnameS  Σ   Firstname_variants
Tor         2   Tor, Thor
Per         4   Per, Peer, Pær, Pär
Karoline    3   Caroline, Charoline, Karoliine

───

I hope I’ve provided all information and asked the question in a manner befitting the RoC of Stackoverflow. Be gentle; it’s my first question!

Canned Man
  • 734
  • 1
  • 7
  • 26
  • Well, what have you tried? You will need to read-up on group by, and pivot function. Just slapping up some requirements and asking for a solution is not going to get you too far. Sorry. – OldProgrammer Aug 14 '16 at 17:00
  • 1
    "Oracle Database using MS Access and LibreOffice Base" This makes somewhere between very little and no sense. Oracle and Access are completely separate and significantly different technologies. Please determine and clarify which one you're using, or alternatively, how you're mixing them. – jpmc26 Aug 14 '16 at 17:04
  • I have tried to address the issues posted by you, and hope it is clearer now. I use LO Base when doing corrections to the transcriptions we’ve received, as this allows us to show a copy of the source image at the bottom of the screen. I use MS Access when doing mass changes. – Canned Man Aug 14 '16 at 19:41

1 Answers1

0
SELECT FirstnameS, COUNT(Firstname) AS Num
FROM myTable
GROUP BY FirstnameS

gives you the first two columns.

The third depends on the database system - can you run Oracle queries (directly or pass-through)?

Edit:

Oracle: SQL Query to concatenate column values from multiple rows in Oracle

MS-Access: Combine values from related rows into a single concatenated string value

Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80
  • Thank you so much! I am unable to access the dbase directly; that is limited to the main tech guy. I am, however, able to for example run maketable-queries and work directly into those sub tables. What would be the difference in running the count on Firstname and FirstnameS? – Canned Man Aug 14 '16 at 19:43
  • @CannedMan: Depending on the number of rows in your table, the Access solution may be pretty slow. Then you'd have to convince your tech guy to run an Oracle SELECT query. -- See examples [here](http://www.dofactory.com/sql/group-by) or [here](http://www.w3resource.com/sql/aggregate-functions/count-with-group-by.php). – Andre Aug 14 '16 at 21:07
  • @CannedMan: BTW, if you have an Oracle user to link tables into Access (I presume read-only), you can also run a Pass-Through SELECT query with this user. That is most certainly the more efficient solution. – Andre Aug 15 '16 at 06:04
  • Your suggestion worked perfectly – thanks a lot! I now have a column showing the standard name and the number of instances this is found. Regarding the third (concatenating the variants), I followed your link to Lou Franco’s reply, and tried running the functions in the Oracle article he linked to. I wasn’t able to, though, getting the error message ‘Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.’ (I tried both the wm_concatform and the list_agg.) – Canned Man Aug 15 '16 at 07:14