1/1
Australian Employee Salary/Wages DATAbase by detailed occupation, location and year (2002-14); (plus Sole Traders)
Version 5 2019-03-04, 04:35
Version 4 2017-07-10, 05:48
Version 3 2017-01-17, 04:32
Version 2 2017-01-06, 00:09
Version 1 2017-01-06, 00:00
dataset
posted on 2019-03-04, 04:35 authored by Richard FerrersRichard Ferrers, Australian Taxation OfficeThe ATO (Australian Tax Office) made a dataset openly available (see links) showing all the Australian Salary and Wages (2002, 2006, 2010, 2014) by detailed occupation (around 1,000) and over 100 SA4 regions. Sole Trader sales and earnings are also provided. This open data (csv) is now packaged into a database (*.sql) with 45 sample SQL queries (backupSQL[date]_public.txt).
See more description at related Figshare #datavis record.
Versions:
V5: Following #datascience course, I have made main data (individual salary and wages) available as csv and Jupyter Notebook. Checksum matches #dataTotals. In 209,xxx rows.
Also provided Jobs, and SA4(Locations) description files as csv. More details at: Where are jobs growing/shrinking? Figshare DOI: 4056282 (linked below). Noted 1% discrepancy ($6B) in 2010 wages total - to follow up.
#dataTotals - Salary and Wages
| Year | Workers (M) | Earnings ($B) | |
|---|---|---|---|
| 2002 | 8.5 | 285 | |
| 2006 | 9.4 | 372 | |
| 2010 | 10.2 | 481 | |
| 2014 | 10.3 | 584 |
#dataTotal - Sole Traders
| Year | Workers (M) | Sales ($B) | Earnings ($B) |
|---|---|---|---|
| 2002 | 0.9 | 61 | 13 |
| 2006 | 1.0 | 88 | 19 |
| 2010 | 1.1 | 112 | 26 |
| 2014 | 1.1 | 96 | 30 |
#links
See ATO request for data at ideascale link below.
See original csv open data set (CC-BY) at data.gov.au link below.
This database was used to create maps of change in regional employment - see Figshare link below (m9.figshare.4056282).
#package
This file package contains a database (analysing the open data) in SQL package and sample SQL text, interrogating the DB. DB name: test. There are 20 queries relating to Salary and Wages.#analysis
The database was analysed and outputs provided on Nectar(.org.au) resources at: http://118.138.240.130.(offline)
This is only resourced for max 1 year, from July 2016, so will expire in June 2017. Hence the filing here. The sample home page is provided here (and pdf), but not all the supporting files, which may be packaged and added later. Until then all files are available at the Nectar URL. Nectar URL now offline - server files attached as package (html_backup[date].zip), including php scripts, html, csv, jpegs.
#install
IMPORT: DB SQL dump e.g. test_2016-12-20.sql (14.8Mb)
1.Started MAMP on OSX.
1.1 Go to PhpMyAdmin
2. New Database:
3. Import: Choose file: test_2016-12-20.sql -> Go (about 15-20 seconds on MacBookPro 16Gb, 2.3 Ghz i5)
4. four tables appeared:
jobTitles 3,208 rows | salaryWages 209,697 rows | soleTrader 97,209 rows | stateNames 9 rows
plus views e.g. deltahair, Industrycodes, states
5. Run test query under **#; Sum of Salary by SA4 e.g. 101 $4.7B, 102 $6.9B
#sampleSQL
select sa4,
(select sum(count) from salaryWages
where year = '2014' and sa4 = sw.sa4) as thisYr14,
(select sum(count) from salaryWages
where year = '2010' and sa4 = sw.sa4) as thisYr10,
(select sum(count) from salaryWages
where year = '2006' and sa4 = sw.sa4) as thisYr06,
(select sum(count) from salaryWages
where year = '2002' and sa4 = sw.sa4) as thisYr02
from salaryWages sw
group by sa4
order by sa4
