Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    28

    Unanswered: help on comparing and modifying a resultset or table

    HI everybody need help badly on the following

    have to recordsets with the following with the following SQL

    SELECT place, count(minus)as countminus
    order by place
    group by place

    THE RESULT WOULD BE

    PLACE countminus

    ABC 10
    DEF 50
    HIJ 5
    KLM 2
    MNO 0

    MY OTHER QUERY IS
    SELECT plus, count(plus) as countplus
    GROUP BY plus
    ORDER BY plus

    THE RESULT WOULD BE:

    PLUS COUNTPLUS

    ABC 10
    DEF 20

    my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...

    with this condition..

    I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...

    THE RESULT WOULD BE THIS.....


    PLACE countminus COUNTPLUS

    ABC 10 10
    DEF 50 20
    HIJ 5 0
    KLM 2 0
    MNO 0 0


    I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PLEASE STOP THINKING ABOUT LOOPS!!!! SQL DOESN'T WORK THAT WAY!!!!
    Code:
    select m.place
         , m.countminus
         , coalesce(p.countplus,0) as countplus
      from (
           select place
                , count(minus) as countminus
             from sometable1
           group by place 
           ) as m
    left outer
      join (
           select plus
                , count(plus) as countplus
             from sometable2
           group by plus 
           ) as p
        on p.plus = m.place
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    28

    problem solved

    thanks so much it solved my problem sorry for the loop frase i mentioned...

    again thanks

Posting Permissions

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