Hello there.

I've a query with returns 2 values: a code and a value.

What I want is to have 3 columns: col1 is the code, col2 is the value if the
code is=1 and col3 is the value if the code is=2.

Also I'd like to have the total of col2 and col3.

How to do so ?

Thanks for helping.

Bob

On 21 Mar, 17:01, "Bob Bedford" <b...@bedford.com> wrote:
> Hello there.
>
> I've a query with returns 2 values: a code and a value.
>
> What I want is to have 3 columns: col1 is the code, col2 is the value if the
> code is=1 and col3 is the value if the code is=2.
>
> Also I'd like to have the total of col2 and col3.
>
> How to do so ?
>
> Thanks for helping.
>
> Bob

What if the code is neither 1 nor 3?

On Mar 21, 5:01 pm, "Bob Bedford" <b...@bedford.com> wrote:
> Hello there.
>
> I've a query with returns 2 values: a code and a value.
>
> What I want is to have 3 columns: col1 is the code, col2 is the value if the
> code is=1 and col3 is the value if the code is=2.
>
> Also I'd like to have the total of col2 and col3.
>
> How to do so ?
>
> Thanks for helping.
>
> Bob

Well here's one way:

SELECT a1.code,a2.value,a3.value
FROM mytable a1
LEFT JOIN mytable a2 ON a1.code = a2.code AND a2.code =1
LEFT JOIN mytable a3 ON a1.code = a3.code AND a3.code =2;

Is 'total' the sum or the count?

On 21 Mar, 17:28, "strawberry" <zac.ca...@gmail.com> wrote:
> On Mar 21, 5:01 pm, "Bob Bedford" <b...@bedford.com> wrote:
>
> > Hello there.
>
> > I've a query with returns 2 values: a code and a value.
>
> > What I want is to have 3 columns: col1 is the code, col2 is the value if the
> > code is=1 and col3 is the value if the code is=2.
>
> > Also I'd like to have the total of col2 and col3.
>
> > How to do so ?
>
> > Thanks for helping.
>
> > Bob
>
> Well here's one way:
>
> SELECT a1.code,a2.value,a3.value
> FROM mytable a1
> LEFT JOIN mytable a2 ON a1.code = a2.code AND a2.code =1
> LEFT JOIN mytable a3 ON a1.code = a3.code AND a3.code =2;
>
> Is 'total' the sum or the count?

You should use COALESCE on that to get just 2 values out.

Or there is the IF function or even possibly in this case the ELT one.

I tried this:

select code,part,
case code
when 0 then (part as col2)
when 1 then (part as col3)
end
from list

but it doesn't work.

Is what you have understood I want to do ?

Bob

On 21 Mar, 17:35, "Bob Bedford" <b...@bedford.com> wrote:
> I tried this:
>
> select code,part,
> case code
> when 0 then (part as col2)
> when 1 then (part as col3)
> end
> from list
>
> but it doesn't work.
>
> Is what you have understood I want to do ?
>
> Bob

I thought code was 1 or 2 not 0 or 1??

SELECT
`code`
CASE `code`
WHEN 1 THEN `col2`
WHEN 2 THEN `col3`
END `part`

FROM `list`

> SELECT
> `code`
> CASE `code`
> WHEN 1 THEN `col2`
> WHEN 2 THEN `col3`
> END `part`
>
> FROM `list`

well it doesn't work but I'll explain again as I don't know if it's clear
enough:

My query returns a code and an amount.

Here are some result rows:
code part
1 50
1 80
2 70
1 20
2 10

the result I'd like is:
code col2 col3
1 50
1 80
2 70
1 20
2 10

So when the code is 1 then the part value must go in col2
when the code is 2 then the part value must go in col3.

You query says that col2 is unknown. They are aliases (new created fields)
only for this query, they are no part of any table.

Bob

Bob Bedford wrote:
> > SELECT
> > `code`
> > CASE `code`
> > WHEN 1 THEN `col2`
> > WHEN 2 THEN `col3`
> > END `part`
> >
> > FROM `list`
>
> well it doesn't work but I'll explain again as I don't know if it's clear
> enough:
>
> My query returns a code and an amount.
>
> Here are some result rows:
> code part
> 1 50
> 1 80
> 2 70
> 1 20
> 2 10
>
> the result I'd like is:
> code col2 col3
> 1 50
> 1 80
> 2 70
> 1 20
> 2 10
>
> So when the code is 1 then the part value must go in col2
> when the code is 2 then the part value must go in col3.
>
> You query says that col2 is unknown. They are aliases (new created fields)
> only for this query, they are no part of any table.
>
> Bob

So what's your Primary Key?

> So what's your Primary Key?
I've simplified the query. I've an other field for primary key, called
idrecord.

Bob Bedford wrote:
> > So what's your Primary Key?
> I've simplified the query. I've an other field for primary key, called
> idrecord.

Well why didn't you say so? So,does this work - except for the
counting bit obviously...

SELECT a1.idrecord,a1.code,a2.value col2,a3.value col3
FROM mytable a1
LEFT JOIN mytable a2 ON a1.idrecord = a2.idrecord AND a2.code =1
LEFT JOIN mytable a3 ON a1.idrecord = a3.idrecord AND a3.code =2;

You could always add a 'WHERE !isnull' type statement for neatness