Member Avatar for kgizo

I have Post code column and I want to get 60, 30 and 10% for each area code that I have, but the thing is there is a between statement which makes things a bit complicated. I need 60% from the first postcode then 30% from the second and 10% from the last postcode i.e these are the people I am trying to pull from each postcode. Here is my code below:

SELECT top 50000 
     id
    ,email 
FROM fn_getsacilist ('knowblet,saciprofiled,govdata,tobydata',25000000,10,0)
WHERE idnumber>='66' 
    and idnumber<='98'
    and (postcode between 2000 and 2199
            or postcode between 4000 and 4099
            or postcode between 7400 and 8099)
    and income>=15000

Now to get results of a specific province then I would pull the data according to the postcodes like this postcode between 0001 and 2199 for instance.

But the request in my particular case is to pull the data in this manner:

JHB-60% // Cape Town-30% // Durban-10% and the only way to do this is by pulling the data by postcode as in my code above.

  • 2 Contributors
  • 3 Replies
  • 238 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rproffitt
Member Avatar for rproffitt

The specification here is unclear but if I were to guess you want groups that correspond to 10% of the total by zip/postal code.

I don't see how I could get that 10% (or other numbers) without some trial and error. That is, you know the total count so let's say that's 1 million entries. To get 10% or 100,000 entries from a zip/postal code range is going to take us trying SQL queries until we get our 100K count result.

Now what if you wanted to change that spec to be "I want to randomly send something to 10%"? That's another question altogether.

For now, since there is only this one case, you should bruteforce the query as it's just this one case.

Member Avatar for kgizo

Yes rproffitt thats exactly what I want, groups that correspond to 10% of the total by zip/postal code.

Member Avatar for rproffitt

Sorry I had a memory lapse here. First read http://www.w3schools.com/sql/sql_top.asp

Aha, you can get the top 10 percent from a query. For your idea sort by zip/postal code.
But the next problem is how to get the other records.

I'm think maybe we have to get the row numbers from 3 top queries. The the record numbers will be used like we read at http://stackoverflow.com/questions/7332731/sql-server-select-top-10-20-results

Sorry if I don't write this for you but the next day and another run at it seems to find it may be doable. For me I'd be doing this in code since that's how I use MySQL. Be sure to check how TOP works in your version of MySQL.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.