On Mon, November 19, 2012 17:02, Shawn Green wrote: > On 11/19/2012 9:02 AM, Mogens Melander wrote: >> >> On Mon, November 19, 2012 13:49, Jan Steinman wrote: >>>> From: "Mogens Melander" <mog...@fumlersoft.dk> >>>> >>>> So, I got a little further with my problem. I found an article >>>> on: >>>> >>>> http://stackoverflow.com/questions/3122424/dynamic-mysql-query-view-for-crosstab >>>> >>>> Describing how to do the dynamic generation of SQL statements. That's >>>> all good, kind of. The resulting SQL looks like this: >>>> >>>> SELECT main.code >>>> , IF(iconstandardrel.icon = 4,1,0) AS 'internationalt_produkt.eps' >>>> , IF(iconstandardrel.icon = 3,1,0) AS 'god_vaerdi.eps' >>>> , IF(iconstandardrel.icon = 2,1,0) AS 'for_miljoeets_skyld.eps' >>>> , IF(iconstandardrel.icon = 1,1,0) AS 'ergonomisk_produkt.eps' >>>> , IF(iconstandardrel.icon = 6,1,0) AS 'saml_selv.eps' >>>> , IF(iconstandardrel.icon = 12,1,0) AS 'brandfarlig.eps' >>>> FROM iconstandardrel >>>> JOIN main ON main.code = iconstandardrel.code >>>> JOIN iconstandard ON iconstandard.id = iconstandardrel.icon >>>> ORDER BY iconstandardrel.code; >>>> >>>> Which produces results like: >>>> >>>> 101577, 1, 0, 0, 0, 0, 0 >>>> 101679, 0, 1, 0, 0, 0, 0 >>>> 101679, 1, 0, 0, 0, 0, 0 >>>> 101681, 1, 0, 0, 0, 0, 0 >>>> 101748, 0, 1, 0, 0, 0, 0 >>>> 101748, 1, 0, 0, 0, 0, 0 >>>> >>>> But I would like to have One line per code: >>>> >>>> 101577, 1, 0, 0, 0, 0, 0 >>>> 101679, 1, 1, 0, 0, 0, 0 >>>> 101681, 1, 0, 0, 0, 0, 0 >>>> 101748, 1, 1, 0, 0, 0, 0 >>>> >>>> Is it possible to achieve this in pure SQL ? >>> >>> I think you need GROUP BY main.code. >>> >> >> No, that ain't it either. I've tried that. >> >> But thanks anyway :) >> >> I can't figure out what to call this operation, to do a search. >> Someone out there must have done this before. >> > > You need both GROUP BY and either SUM or MAX, like this > > SELECT > ... > , SUM(IF(iconstandardrel.icon = 3,1,0)) AS 'god_vaerdi.eps' > ... > GROUP BY main.code; > > > or > > SELECT > ... > , MAX(IF(iconstandardrel.icon = 3,1,0)) AS 'god_vaerdi.eps' > ... > GROUP BY main.code; > > That will combine (aggregate) your rows together. > -- > Shawn Green
Super cool. The SUM() GROUP BY did the trick. Thanks a lot. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
