Australian Employee Salary/Wages DATAbase by detailed occupation, location and year (2002-14); (plus Sole Traders)

2017-07-10T05:48:27Z (GMT) by Richard Ferrers ATO
<div>The 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).</div><div><br></div><div>See more description at related Figshare #datavis record. </div><div><br></div><div><p>#dataTotals - Salary and Wages</p><table><tbody><tr><th>Year</th><th>Workers (M)</th><th>Earnings ($B)</th><th> </th></tr><tr><td>2002</td><td>8.5</td><td>285</td><td><br></td></tr><tr><td>2006</td><td>9.4</td><td>372</td><td><br></td></tr><tr><td>2010</td><td>10.2</td><td>481</td><td><br></td></tr><tr><td>2014</td><td><a href="http://118.138.240.130/testSalWages2014num.php">10.3</a></td><td><a href="http://118.138.240.130/testSalWages2014total.php">584</a><br></td><td><br></td></tr></tbody></table></div><div><br></div><div>#dataTotal - Sole Traders</div><div><table><tbody><tr><th>Year</th><th>Workers (M)</th><th>Sales ($B)</th><th>Earnings ($B)</th></tr><tr><td>2002</td><td>0.9</td><td>61</td><td>13</td></tr><tr><td>2006</td><td>1.0</td><td>88</td><td>19</td></tr><tr><td>2010</td><td>1.1</td><td>112</td><td>26</td></tr><tr><td>2014</td><td>1.1</td><td><a href="http://118.138.240.130/testST2014total.php">96</a></td><td>30</td></tr></tbody></table></div><div><br></div><div>#links </div><div>See ATO request for data at ideascale link below.</div><div>See original csv open data set (CC-BY) at data.gov.au link below.</div><div>This database was used to create maps of change in regional employment - see Figshare link below (m9.figshare.4056282).</div><div><br></div><div>#package</div>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.<div><br></div><div>#analysis</div><div>The database was analysed and outputs provided on Nectar(.org.au) resources at: http://118.138.240.130.(offline)</div><div>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.</div><div><br></div><div>#install</div><div><div>IMPORT: DB SQL dump e.g. test_2016-12-20.sql (14.8Mb)</div><div>1.Started MAMP on OSX.</div><div>1.1 Go to PhpMyAdmin</div><div>2. New Database: </div><div>3. Import: Choose file: test_2016-12-20.sql -> Go (about 15-20 seconds on MacBookPro 16Gb, 2.3 Ghz i5)</div><div>4. four tables appeared: </div><div>jobTitles 3,208 rows | salaryWages 209,697 rows | soleTrader 97,209 rows | stateNames 9 rows</div><div>plus views e.g. deltahair, Industrycodes, states</div><div>5. Run test query under **#; Sum of Salary by SA4 e.g. 101 $4.7B, 102 $6.9B</div></div><div><br></div><div>#sampleSQL</div><div><div>select sa4,</div><div><br></div><div>(select sum(count) from salaryWages</div><div>where year = '2014' and sa4 = sw.sa4) as thisYr14,</div><div><br></div><div>(select sum(count) from salaryWages</div><div>where year = '2010' and sa4 = sw.sa4) as thisYr10,</div><div><br></div><div>(select sum(count) from salaryWages</div><div>where year = '2006' and sa4 = sw.sa4) as thisYr06,</div><div><br></div><div>(select sum(count) from salaryWages</div><div>where year = '2002' and sa4 = sw.sa4) as thisYr02</div><div><br></div><div>from salaryWages sw</div><div><br></div><div>group by sa4</div><div>order by sa4 </div></div>