second times sql and SUM function in Map script

[Murat Beyhan]

Dear Milo,
Thank you for your interest.
Mysql runs under my localhost that version is 4.1.18.
So this version of MySql does not support view function..
so far I have try to create a view at same database which the version is 
5.0 and runs at another computer.
When I try to create the view I have been warned by the computer to add 
group clause to the sql statement.
when I add group statement to the script It could created view. Then

So I have tried to add group clause to the sql statement which I forgot 
it before in my localhost.
 Then My map file draw map without any problem. I hope I explained what 
I have done.
<SrcSQL>SELECT m_village1.latitude,m_village1.longitude, 
sum(d_damage.cmpdb_rc+d_damage.prtdb_rc) as total_rc from d_damage, 
m_village1 where d_damage.eq_id like "%eq_id%" and 
d_damage.region_id=m_village1.dist_id group by d_damage.region_id</SrcSQL>

Regards...
Murat

Milo van der Linden wrote:
> Hello Murat!
>
> What I would do (as I always do when queries get complex) is create a 
> view in the mysql database and call that view. That will speed up 
> performance and give you less chance of error in your mapfile.
>
> So in MySQL:
>
> CREATE OR REPLACE VIEW MyVIEW as
> SELECT
> d_damage as damage,
> m_village1.name as villagename,
> m_village1.latitude as latitude,
> m_village1.longitude as longitude,
> sum(d_damage.deadp_rc + d_damage.deadp_wood + d_damage.deadp_briq + 
> d_damage.deadp_brick + d_damage.deadp_stone + d_damage.deadp_adobe) as 
> totaldamage
> from
>     d_damage,
>     m_village1
> where
> d_damage.region_id=m_village1.dist_id
>
> And in the MapFile;
> select latitude, longitude, totaldamage from myVIEW where 
> d_damage.eq_id like "%eq_id%" and m_village1.name not like "%(%"
>
>
> This will make life less complicated, plus you can test the query 
> better in mySQL!
>
>
> Murat Beyhan schreef:
>> Dear Friends,
>>
>> I would like to draw a casualty distribution map for  various 
>> building types.
>>
>> On the srcSQL as you see  I need summation of the casualties of each 
>> type of buildings(rc,wood,briq,brick,stone,adobe)
>> So I have used sum function to obtain total number of casualties. But 
>> when I tried to make legend I have to select classitem so I have used 
>> AS totald for that reason
>> then I have used totald as a classitem.
>> But mapserver returns an error message.
>>
>> When I select only one item for select query for example select 
>> d_damage.deadp_wood from d_damage
>> then If I use deadp_wood as classitem the query works and the map drawn.
>>
>> For my intend what I have to do?
>> Regards...
>> MURAT
>>
>> script: as follows
>>
>>
>> LAYER
>> NAME "Dhuman"
>>   CONNECTIONTYPE OGR    CONNECTION '<OGRVRTDataSource>
>>   <OGRVRTLayer name="Dhuman">
>> <SrcDataSource>MYSQL:turkey,user=root,password=mysql_123,host=localhost,port=3306,tables=d_damage 
>> m_village1</SrcDataSource>
>>       <SrcSQL>SELECT m_village1.latitude,m_village1.longitude, 
>> *sum(d_damage.deadp_rc + d_damage.deadp_wood + d_damage.deadp_briq + 
>> d_damage.deadp_brick + d_damage.deadp_stone + d_damage.deadp_adobe) 
>> as totald *from d_damage, m_village1 where d_damage.eq_id like 
>> "%eq_id%" and d_damage.region_id=m_village1.dist_id and 
>> m_village1.name not like "%(%"</SrcSQL>
>>       <GeometryType>wkbPoint</GeometryType>
>>       <GeometryField encoding="PointFromColumns" x="longitude" 
>> y="latitude"/>
>>   </OGRVRTLayer>
>> </OGRVRTDataSource>'
>>  TYPE POINT
>>   DATA Dhuman
>>   STATUS default
>>           CLASSITEM "totald"
>>   CLASS
>>   NAME "HUMAN DAMAGE"
>>   EXPRESSION ([totald] < 0)
>> #    COLOR 255 0 255
>> #    SYMBOL "cpoint"
>> #    SIZE 12
>> END
>>
>>
>>
>>   CLASS
>>   NAME "1-10"
>>   EXPRESSION ([totald] GE 1.0 AND [totald] LT 10.0)
>>  SYMBOL "cpoint"
>>  SIZE 5
>>   COLOR 255 250 250
>> END
>>
>>   CLASS
>>   NAME "11-50"
>>   EXPRESSION ([totald] GE 11.0 AND [totald] LT 50.0)
>>  SYMBOL "cpoint"
>>  SIZE 5
>>   COLOR 248 248 255
>> END
>>
>> CLASS
>>   NAME "51-100"
>>   EXPRESSION ([totald] GE 51.0 AND [totald] LT 100.0)
>>  SYMBOL "cpoint"
>>  SIZE 5
>>   COLOR 255 250 240
>> END
>> CLASS
>>   NAME "101-200"
>>   EXPRESSION ([totald] GE 101.0 AND [totald] LT 200.0)
>>  SYMBOL "cpoint"
>>  SIZE 5
>>   COLOR 102 255 204
>> END
>> CLASS
>>   NAME "201-400"
>> EXPRESSION ([totald] GE 201.0 AND [totald] LT 400.0)
>>   SYMBOL "cpoint"
>>   SIZE 5
>>   COLOR 102 53 0
>> END
>> CLASS
>>   NAME "400<"
>>    EXPRESSION ([totald] GT 400.0)
>>   SYMBOL "cpoint"
>>   SIZE 5
>>   COLOR 102 153 255
>> END
>> END
>>
>>
>>
>> the error message as follows:
>>
>> msDrawMap(): Image handling error. Failed to draw layer named 'Dhuman'.
>> msOGRFileOpen(): OGR error. Open failed for OGR connection ` 
>> MYSQL:turkey,user=root,password=mysql_123,host=localhost,port=3306,tables=d_damage 
>> m_village1 SELECT m_village1.latitude,m_village1.longitude, 
>> sum(d_damage.deadp_rc+ d_damage.deadp_wood +d_damage.deadp_briq + 
>> d_damage.deadp_brick + d_damage.deadp_stone + d_damage.deadp_adobe) 
>> as totald from d_damage, m_village1 where d_damage.eq_id like 
>> "200006060242-auto-000" and d_damage.region_id=m_village1.dist_id and 
>> m_village1.name not like "%(%" wkbPoint '. SQL statement failed, or 
>> returned no layer result: SELECT 
>> m_village1.latitude,m_village1.longitude, sum(d_damage.deadp_rc+ 
>> d_damage.deadp_wood +d_damage.deadp_briq + d_damage.deadp_brick + 
>> d_damage.deadp_stone + d_damage.deadp_adobe) as totald from d_damage, 
>> m_village1 where d_damage.eq_id like "200006060242-auto-000" and 
>> d_damage.region_id=m_village1.dist_id and m_village1.name not like "%(%"
>>
>>
>
>
> -- 
>
>
> 	
>
> Milo van der Linden
> skype: milovanderlinden <skype:milovanderlinden?add>
> mlinden at zeelandnet.nl <mailto:mlinden at zeelandnet.nl>
> milovanderlinden at gmail.com <mailto:milovanderlinden at gmail.com>
> milo at 3dsite.nl <mailto:milo at 3dsite.nl>
> http://www.3dsite.nl
>
> 	  	
>
> De informatie in dit bericht reflecteert mijn persoonlijke mening en 
> niet die van een bedrijf of instantie. Aan de informatie kunnen geen 
> rechten worden ontleend. Indien dit bericht onderdeel is van een 
> forum, mailing-list of community dan gelden automatisch de bij het 
> betreffende medium behorende voorwaarden. The information in this 
> message reflects my personal opinion and not that of a company or 
> public body. All rights reserved.If this message is contained in a 
> mailing-list or community, the rights on the medium are automatically 
> adapted.
>
>
> -- 
> This message has been scanned for viruses and
> dangerous content by *MailScanner* <http://www.mailscanner.info/>, and is
> believed to be clean. 


-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.