List:General Discussion« Previous MessageNext Message »
From:Mogens Melander Date:November 20 2012 1:45pm
Subject:Re: Dynamic crosstab got me lost.
View as plain text  
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" <mogens@stripped>
>>>>
>>>> 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.

Thread
Dynamic crosstab got me lost.Mogens Melander14 Nov
  • RE: Dynamic crosstab got me lost.Rick James14 Nov
  • RE: Dynamic crosstab got me lost.Mogens Melander14 Nov
    • Re: Dynamic crosstab got me lost.Peter Brawley15 Nov
  • Re: Dynamic crosstab got me lost.Mogens Melander19 Nov
    • Re: Dynamic crosstab got me lost.hsv20 Nov
Re: Dynamic crosstab got me lost.Jan Steinman19 Nov
  • Re: Dynamic crosstab got me lost.Mogens Melander19 Nov
    • Re: Dynamic crosstab got me lost.Shawn Green19 Nov
      • Re: Dynamic crosstab got me lost.Mogens Melander20 Nov
  • Re: Dynamic crosstab got me lost.hsv19 Nov