Hi scott, > In summary, the first thing I would like to do is count the number of > votes for 'A', and count the number of votes for 'B', both per county and > based upon a user entered datesubmitted range;
a query like this: SELECT countyid,vote,count(*) as count FROM votes WHERE vote='A' GROUP BY countyid, vote ORDER BY count desc, countyid asc allow you to count the votes for 'A'. You can relate the query with the response table adding a where clause on "responseid" and then filter by the 'datesubmitted' field: SELECT countyid,vote,count(*) as votes_for_A FROM votes, response WHERE vote='A' AND response.responseid = votes.responseid AND response.datesubmitted BETWEEN <lower datetime> AND <upper datetime> GROUP BY countyid, vote ORDER BY count desc, countyid asc now you can simbolize the map according to the "votes_for_A" alias field. > produce a percentage of votes for 'A', per county, based upon the total > votes for A and B for each county. How to obtain this with a single query, is a little over my knowledge. I'll try to find... ciao ............................pg -- Pietro Giannini Bytewise srl - Area GIS 41°50'38.58"N 12°29'13.39"E On Gio, Ottobre 30, 2008 15:54, Scott Pezanowski wrote: > Hi Pietro, > > Thank you for your response. Sure, I will try below to add some sample > data that illustrates what I am trying to do. I have a "votes" table which > has a vote for each county (A versus B). This can be joined by a > responseid to a table "response", which has the date in which the votes > were submitted. Also, countyid references my geometry in a separate table. > In summary, the first thing I would like to do is count the number of > votes for 'A', and count the number of votes for 'B', both per county and > based upon a user entered datesubmitted range; And then symbolize my map > based upon these results. Also, and I guess ideally, I'd like to also > produce a percentage of votes for 'A', per county, based upon the total > votes for A and B for each county. And then color code my counties based > upon this percentage. > > Thank you for help you may provide. > > Scott > > Below is some sample data: > > votes table: > > countyid;vote;responseid > 1;"B";"296532B5A40C493592C9E6B480E15E37" > 2;"A";"296532B5A40C493592C9E6B480E15E37" > 3;"B";"296532B5A40C493592C9E6B480E15E37" > 4;"B";"296532B5A40C493592C9E6B480E15E37" > 5;"B";"296532B5A40C493592C9E6B480E15E37" > 6;"B";"296532B5A40C493592C9E6B480E15E37" > 7;"B";"296532B5A40C493592C9E6B480E15E37" > 8;"B";"296532B5A40C493592C9E6B480E15E37" > 9;"A";"296532B5A40C493592C9E6B480E15E37" > 10;"B";"296532B5A40C493592C9E6B480E15E37" > 11;"B";"296532B5A40C493592C9E6B480E15E37" > 12;"B";"296532B5A40C493592C9E6B480E15E37" > 13;"B";"296532B5A40C493592C9E6B480E15E37" > 14;"A";"296532B5A40C493592C9E6B480E15E37" > 15;"B";"296532B5A40C493592C9E6B480E15E37" > 16;"B";"296532B5A40C493592C9E6B480E15E37" > 17;"B";"296532B5A40C493592C9E6B480E15E37" > 18;"B";"296532B5A40C493592C9E6B480E15E37" > 19;"B";"296532B5A40C493592C9E6B480E15E37" > 20;"B";"296532B5A40C493592C9E6B480E15E37" > 21;"B";"296532B5A40C493592C9E6B480E15E37" > 22;"B";"296532B5A40C493592C9E6B480E15E37" > 23;"A";"296532B5A40C493592C9E6B480E15E37" > 24;"A";"296532B5A40C493592C9E6B480E15E37" > 25;"A";"296532B5A40C493592C9E6B480E15E37" > 26;"A";"296532B5A40C493592C9E6B480E15E37" > 27;"B";"296532B5A40C493592C9E6B480E15E37" > 28;"B";"296532B5A40C493592C9E6B480E15E37" > 29;"B";"296532B5A40C493592C9E6B480E15E37" > 30;"A";"296532B5A40C493592C9E6B480E15E37" > 31;"B";"296532B5A40C493592C9E6B480E15E37" > 32;"B";"296532B5A40C493592C9E6B480E15E37" > 33;"B";"296532B5A40C493592C9E6B480E15E37" > 34;"B";"296532B5A40C493592C9E6B480E15E37" > 35;"A";"296532B5A40C493592C9E6B480E15E37" > 36;"B";"296532B5A40C493592C9E6B480E15E37" > 37;"A";"296532B5A40C493592C9E6B480E15E37" > 38;"B";"296532B5A40C493592C9E6B480E15E37" > 39;"A";"296532B5A40C493592C9E6B480E15E37" > 40;"A";"296532B5A40C493592C9E6B480E15E37" > 41;"B";"296532B5A40C493592C9E6B480E15E37" > 42;"B";"296532B5A40C493592C9E6B480E15E37" > 43;"A";"296532B5A40C493592C9E6B480E15E37" > 44;"B";"296532B5A40C493592C9E6B480E15E37" > 45;"B";"296532B5A40C493592C9E6B480E15E37" > 46;"A";"296532B5A40C493592C9E6B480E15E37" > 47;"B";"296532B5A40C493592C9E6B480E15E37" > 48;"B";"296532B5A40C493592C9E6B480E15E37" > 49;"B";"296532B5A40C493592C9E6B480E15E37" > 50;"B";"296532B5A40C493592C9E6B480E15E37" > 51;"A";"296532B5A40C493592C9E6B480E15E37" > 52;"B";"296532B5A40C493592C9E6B480E15E37" > 53;"B";"296532B5A40C493592C9E6B480E15E37" > 54;"B";"296532B5A40C493592C9E6B480E15E37" > 55;"B";"296532B5A40C493592C9E6B480E15E37" > 56;"B";"296532B5A40C493592C9E6B480E15E37" > 57;"B";"296532B5A40C493592C9E6B480E15E37" > 58;"B";"296532B5A40C493592C9E6B480E15E37" > 59;"B";"296532B5A40C493592C9E6B480E15E37" > 60;"B";"296532B5A40C493592C9E6B480E15E37" > 61;"B";"296532B5A40C493592C9E6B480E15E37" > 62;"B";"296532B5A40C493592C9E6B480E15E37" > 63;"A";"296532B5A40C493592C9E6B480E15E37" > 64;"B";"296532B5A40C493592C9E6B480E15E37" > 65;"B";"296532B5A40C493592C9E6B480E15E37" > 66;"B";"296532B5A40C493592C9E6B480E15E37" > 67;"B";"296532B5A40C493592C9E6B480E15E37" > 1;"B";"AE2E9A609C65420A9DDF37A9E56A25A8" > 2;"B";"AE2E9A609C65420A9DDF37A9E56A25A8" > 3;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 4;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 5;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 6;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 7;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 8;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 9;"B";"AE2E9A609C65420A9DDF37A9E56A25A8" > 10;"B";"AE2E9A609C65420A9DDF37A9E56A25A8" > 11;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 12;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 13;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 14;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 15;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 16;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 17;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 18;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 19;"B";"AE2E9A609C65420A9DDF37A9E56A25A8" > 20;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 21;"B";"AE2E9A609C65420A9DDF37A9E56A25A8" > 22;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 23;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 24;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 25;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 26;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 27;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 28;"B";"AE2E9A609C65420A9DDF37A9E56A25A8" > 29;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 30;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 31;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 32;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > 33;"A";"AE2E9A609C65420A9DDF37A9E56A25A8" > > > response table: > > responseid;datesubmitted > "296532B5A40C493592C9E6B480E15E37";"2008-10-13 18:27:28-04" > "AE2E9A609C65420A9DDF37A9E56A25A8";"2008-10-14 14:01:59-04" > "24165287C9D94F9CA9F6C6BBDEB4622B";"2008-10-13 14:52:40-04" > > > > ********************************************* Scott Pezanowski > > > email: [EMAIL PROTECTED] > ********************************************* > >> Date: Thu, 30 Oct 2008 11:22:48 +0100 >> Subject: Re: [mapserver-users] symbolize a wms map based upon a sql >> count() query >> From: [EMAIL PROTECTED] >> To: mapserver-users@lists.osgeo.org >> CC: [EMAIL PROTECTED] >> >> Maybe a view on postresql, with both count and geometry? >> can you pls post a little sample data? >> ciao >> ............................pg >> >> -- >> Pietro Giannini >> Bytewise srl - Area GIS >> 41°50'38.58"N 12°29'13.39"E >> >> >> >> On Gio, Ottobre 30, 2008 03:52, Scott Pezanowski wrote: >> > Hi, >> > >> > I am hoping to be able to symbolize a WMS or image map, based upon the >> > results of a SQL COUNT() query. To be more specific, this query needs >> to >> > be dynamic based upon user input from my application for specific >> > attributes in the data, count up the number of records that meet that >> > query, and then produce a choropleth map of the resulting counts. So >> the >> > query would produce, a count of 10 records for one county, 5 records >> for >> > another county, etc. and my counties will be colored based upon a >> scheme >> > for these counts. My data is in a PostGIS database. >> > >> > Is there a way to do this with Mapserver? I am currently simply >> returning >> > all of the records that meet my criteria and counting things up on the >> > client application. But I am hoping there is a way to do this on the >> > server or database-side of things. >> > >> > Thank you very much in advance for any help or tips you may be able to >> > offer. Also, please let me know if you need more info on what I am >> trying >> > to do. >> > >> > Thanks, >> > Scott >> > >> > >> > >> > >> > ********************************************* Scott Pezanowski >> > >> > >> > email: [EMAIL PROTECTED] >> > ********************************************* >> > _______________________________________________ >> > mapserver-users mailing list >> > mapserver-users@lists.osgeo.org >> > http://lists.osgeo.org/mailman/listinfo/mapserver-users >> > >> > _______________________________________________ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users