Professional Web Applications Themes

if in a query - MySQL

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...

  1. #1

    Default if in a query

    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



    Bob Guest

  2. #2

    Default Re: if in a query

    On 21 Mar, 17:01, "Bob Bedford" <com> wrote: 

    What if the code is neither 1 nor 3?

    Captain Guest

  3. #3

    Default Re: if in a query

    On Mar 21, 5:01 pm, "Bob Bedford" <com> wrote: 

    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?

    strawberry Guest

  4. #4

    Default Re: if in a query

    On 21 Mar, 17:28, "strawberry" <com> wrote: 






    >
    > 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?[/ref]

    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.

    Captain Guest

  5. #5

    Default Re: if in a query

    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



    Bob Guest

  6. #6

    Default Re: if in a query

    On 21 Mar, 17:35, "Bob Bedford" <com> wrote: 

    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`

    Captain Guest

  7. #7

    Default Re: if in a query

    > SELECT 

    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 Guest

  8. #8

    Default Re: if in a query

    Bob Bedford wrote: 
    >
    > 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[/ref]

    So what's your Primary Key?

    strawberry Guest

  9. #9

    Default Re: if in a query

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



    Bob Guest

  10. #10

    Default Re: if in a query

    Bob Bedford wrote: 
    > I've simplified the query. I've an other field for primary key, called
    > idrecord.[/ref]

    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

    strawberry Guest

  11. #11

    Default Re: if in a query

    Hi strawberry,

    it works fine but the problem is that I do a lot of calculations (I wanted
    to understand how it woks then apply to my database).

    Problem is when I do calculation and values are null. In my case I do some
    (sum(a2.value)+sum(a3.value)) and if either one is null then the result is
    null.

    How to do for having the null considered as 0 instead ?

    Thanks for help



    Bob Guest

  12. #12

    Default Re: if in a query

    using ifnull...sorry.

    "Bob Bedford" <com> a écrit dans le message de news:
    4601b9f0$0$3811$sunrise.ch... 



    Bob Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139