Sql Query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Piedro
    #1

    Sql Query

    Hello

    I don't know if anyone in this group can help me but I've got a very
    difficult sql query question.
    I've got a table with 2 fields: A field called field and one called
    value
    it loos like this
    FIELD VALUE
    X00 0
    X01 1000
    X02 2000
    X10 0
    X11 1000
    X12 2000
    Y00 0
    Y01 500
    Y10 0
    Y11 500

    Now out of this table I need the number of records where the values
    are equal when the first letter of field equals 'X' or 'Y' and the 2nd
    char from the field field differ and the 3th char from the field field
    are the same.
    So for example:
    X01 and X11 because first char X = X , 2nd char differs 0<>1 and 3th
    is equal 1 = 1 and the values are equal 1000 = 1000
    But not
    X01 and Y11 because X <> Y

    I hope someone can help with a query for this
    thanks a lot
    PP
    Tags: None
    • Stan Sainte-Rose
      #2
      Re: Sql Query


      Maybe you have to play with the SUBSTRING
      Something like

      SELECT
      Total=(SELECT COUNT(*) from yourtable where
      SUBSTRING(FIELD , 1, 1)= SUBSTRING(T.FIE LD,1, 1) AND
      SUBSTRING(FIELD , 2, 1)<> SUBSTRING(T.FIE LD,2, 1) AND
      SUBSTRING(FIELD , 3, 1)= SUBSTRING(T.FIE LD,3, 1)
      )
      FROM YourTable T

      Stan


      "Piedro" <pproost@hotmai l.com> a écrit dans le message de news:
      b7e3650a.040402 0007.545883a6@p osting.google.c om...[color=blue]
      > Hello
      >
      > I don't know if anyone in this group can help me but I've got a very
      > difficult sql query question.
      > I've got a table with 2 fields: A field called field and one called
      > value
      > it loos like this
      > FIELD VALUE
      > X00 0
      > X01 1000
      > X02 2000
      > X10 0
      > X11 1000
      > X12 2000
      > Y00 0
      > Y01 500
      > Y10 0
      > Y11 500
      >
      > Now out of this table I need the number of records where the values
      > are equal when the first letter of field equals 'X' or 'Y' and the 2nd
      > char from the field field differ and the 3th char from the field field
      > are the same.
      > So for example:
      > X01 and X11 because first char X = X , 2nd char differs 0<>1 and 3th
      > is equal 1 = 1 and the values are equal 1000 = 1000
      > But not
      > X01 and Y11 because X <> Y
      >
      > I hope someone can help with a query for this
      > thanks a lot
      > PP[/color]


        Comment

        • Stan Sainte-Rose
          #3
          Re: Sql Query


          Maybe you have to play with the SUBSTRING
          Something like

          SELECT
          Total=(SELECT COUNT(*) from yourtable where
          SUBSTRING(FIELD , 1, 1)= SUBSTRING(T.FIE LD,1, 1) AND
          SUBSTRING(FIELD , 2, 1)<> SUBSTRING(T.FIE LD,2, 1) AND
          SUBSTRING(FIELD , 3, 1)= SUBSTRING(T.FIE LD,3, 1)
          )
          FROM YourTable T

          Stan


          "Piedro" <pproost@hotmai l.com> a écrit dans le message de news:
          b7e3650a.040402 0007.545883a6@p osting.google.c om...[color=blue]
          > Hello
          >
          > I don't know if anyone in this group can help me but I've got a very
          > difficult sql query question.
          > I've got a table with 2 fields: A field called field and one called
          > value
          > it loos like this
          > FIELD VALUE
          > X00 0
          > X01 1000
          > X02 2000
          > X10 0
          > X11 1000
          > X12 2000
          > Y00 0
          > Y01 500
          > Y10 0
          > Y11 500
          >
          > Now out of this table I need the number of records where the values
          > are equal when the first letter of field equals 'X' or 'Y' and the 2nd
          > char from the field field differ and the 3th char from the field field
          > are the same.
          > So for example:
          > X01 and X11 because first char X = X , 2nd char differs 0<>1 and 3th
          > is equal 1 = 1 and the values are equal 1000 = 1000
          > But not
          > X01 and Y11 because X <> Y
          >
          > I hope someone can help with a query for this
          > thanks a lot
          > PP[/color]


            Comment

            • Rob Panosh
              #4
              Re: Sql Query

              Hope this helps ...

              SELECT count(*)
              FROM myTable
              WHERE substring( field, 1, 1) IN ( 'X', 'Y' ) AND
              ( substring(field , 2, 1 ) <> substring substring( field, 3,
              1 ) OR
              substring(field , 2, 1 ) = substring substring(
              field, 3, 1 ) )

              Regards,
              Rob Panosh

              "Piedro" <pproost@hotmai l.com> wrote in message
              news:b7e3650a.0 404020007.54588 3a6@posting.goo gle.com...[color=blue]
              > Hello
              >
              > I don't know if anyone in this group can help me but I've got a very
              > difficult sql query question.
              > I've got a table with 2 fields: A field called field and one called
              > value
              > it loos like this
              > FIELD VALUE
              > X00 0
              > X01 1000
              > X02 2000
              > X10 0
              > X11 1000
              > X12 2000
              > Y00 0
              > Y01 500
              > Y10 0
              > Y11 500
              >
              > Now out of this table I need the number of records where the values
              > are equal when the first letter of field equals 'X' or 'Y' and the 2nd
              > char from the field field differ and the 3th char from the field field
              > are the same.
              > So for example:
              > X01 and X11 because first char X = X , 2nd char differs 0<>1 and 3th
              > is equal 1 = 1 and the values are equal 1000 = 1000
              > But not
              > X01 and Y11 because X <> Y
              >
              > I hope someone can help with a query for this
              > thanks a lot
              > PP[/color]


                Comment

                • Rob Panosh
                  #5
                  Re: Sql Query

                  Hope this helps ...

                  SELECT count(*)
                  FROM myTable
                  WHERE substring( field, 1, 1) IN ( 'X', 'Y' ) AND
                  ( substring(field , 2, 1 ) <> substring substring( field, 3,
                  1 ) OR
                  substring(field , 2, 1 ) = substring substring(
                  field, 3, 1 ) )

                  Regards,
                  Rob Panosh

                  "Piedro" <pproost@hotmai l.com> wrote in message
                  news:b7e3650a.0 404020007.54588 3a6@posting.goo gle.com...[color=blue]
                  > Hello
                  >
                  > I don't know if anyone in this group can help me but I've got a very
                  > difficult sql query question.
                  > I've got a table with 2 fields: A field called field and one called
                  > value
                  > it loos like this
                  > FIELD VALUE
                  > X00 0
                  > X01 1000
                  > X02 2000
                  > X10 0
                  > X11 1000
                  > X12 2000
                  > Y00 0
                  > Y01 500
                  > Y10 0
                  > Y11 500
                  >
                  > Now out of this table I need the number of records where the values
                  > are equal when the first letter of field equals 'X' or 'Y' and the 2nd
                  > char from the field field differ and the 3th char from the field field
                  > are the same.
                  > So for example:
                  > X01 and X11 because first char X = X , 2nd char differs 0<>1 and 3th
                  > is equal 1 = 1 and the values are equal 1000 = 1000
                  > But not
                  > X01 and Y11 because X <> Y
                  >
                  > I hope someone can help with a query for this
                  > thanks a lot
                  > PP[/color]


                    Comment

                    Previous template Next
                    Working...