Slamdata , MongoDB ----- Creating an analytics workbook for UK land registry data - Help needed with GROUP query

[kum...@gmail.com]

Dear Slamdata users

I am trying to create a few example exercises for a workbook on Slamdata and MongoDB. 

Installing and writing basic queries was a breeze with Slamdata .... it takes a few minutes compared with traditional database like Oracle.

I have got about 4 query outputs with screenshots . I have posted the screenshots on a wordpress page http://emptrics.wordpress.com

Basic data looks like this in the attached jpeg :

Thanks in advance for any help you can offer

Rgds

Vinoo

[Damon LaCaille]

Vinoo,

It would be helpful if you posted the SQL you are struggling with, that way we can help troubleshoot it.  SlamData's SQL² supports GROUP BY.  Find out more starting here.

Thanks,

-Damon

[kum...@gmail.com]

Damon

Thanks for your reply. In Oracle I used to create summary tables to achieve results, export it to excel and build some simple formulae 

because nested grouped bys weren't supported then.

Data example : Data is grouped by flatno/houseno, postcode and sold price history.

1) Flat 618, Point West, 116, Cromwell Road, London, Greater London SW7 4XF

£435,000 Flat, Leasehold 17 Mar 2015

£345,000 Flat, Leasehold 12 Aug 2013

£286,500 Flat, Leasehold 24 Apr 2007

£215,000 Flat, Leasehold 18 Feb 2005

£167,000 Flat, Leasehold 06 Oct 2000

£125,000 Flat, Leasehold 19 Aug 1999

£98,800 Flat, Leasehold (New Build) 20 Apr 1999

2) Flat 610, Point West, 116, Cromwell Road, London, Greater London SW7 4XF

£538,000 Flat, Leasehold 21 Nov 2014

£460,000 Flat, Leasehold 20 Apr 2011

£377,500 Flat, Leasehold 22 Aug 2007

£125,000 Flat, Leasehold (New Build) 20 Apr 1999

So as an end result I want to show that 

Profit No. of. days Approx years Rate of return

Flat 618   336200   5810   15.91 340%

Flat 610 413000 5694   15.6 330%   

USA equivalent data :

1810 Avante Dr, Cedar Park, TX 78613

Price History

DATE EVENT PRICE

04/30/17 Listed for sale $269,000

12/18/06 Sold --

01/13/06 Sold: $175,062

Profit No. of. days Approx years Rate of return

1810 Avante Dr   93938   4125   11.30 53%

However there are cities for example Detroit and even Mountain House , Northern California where property prices have negative growth value, 

but through an aggregated data query and possibility of graphical depiction using Slamdata accurate indices can be showcased.

This is the objective of my exercise.

Thanks and Regards

Vinoo

[kum...@gmail.com]

If I am able to produce these results City Wise and then link it to earnings at a later stage it would 

highlight cities as follows in a pictorial graph :

 

1. Cities house prices continued to increase more quickly than earnings

2. Cities house prices continued to be stable with earnings

[do...@slamdata.com]

Hi Vinoo!

I think I get where you're coming from. Would it be possible to dump out the data you're using and share it? With that we can give you more direct pointers on how to formulate queries.

Cheers!

~Doug

[kum...@gmail.com]

Doug

Thanks for your reply.

Data file location is http://prod.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv

Other subset data files at location https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads

The land registry csv file does not have a header line , so please insert this as header 

“Unique transaction code”,”Price Paid”,”Data of transfer”,”Postcode”,”Property Type”,”Old/New”,”Duration”,”PAON”,”SAON”,”Street”,”Locality”,”Town/City”, “Local Authority”,”County”,”Record Status”

Used command mongoimport -d mydb -c ukpropdata –type csv –file pp-complete.csv –headerline 

to create the collection in mongodb

Rgds

Vinoo

[kum...@gmail.com]

Slamdata - UK sales by county Mar 2017 production data example

SELECT mar.County, count(*) AS SALES

FROM `/mongolocal/mydb/mar2017`

as mar

group by mar.County

[kum...@gmail.com]

Average Sale price by County  Mar 2017 production data example