Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: help on select count

    hi,
    I've these tables:

    TAB1:

    USER_ID......SOC......ANSWER......POINT
    11.........AAAA........4.............88
    11.........AAAA........5..............
    11.........AAAA........6..............
    11.........AAAA........7..............
    11.........AAAA........8............66
    .......................................23 rows

    21.........BBBB........4.............88
    21.........BBBB........5..............
    21.........BBBB........6..............
    21.........BBBB........7..............
    21.........BBBB........8............66
    .......................................23 rows

    27.........AAAA........4.............88
    27.........AAAA........5..............
    27.........AAAA........6..............
    27.........AAAA........7..............
    27.........AAAA........8............66
    .......................................23 rows
    ............................................
    .............................................25070 total rows

    TAB2:

    USER_ID..........AREA_T
    11...............NORD
    21...............CENTRO
    27...............SUD
    26...............NORD
    34...............NORD
    ......................
    ......................
    ......................1090 total rows



    TAB3:

    MACROS.............ANSWER
    CRM.....................4

    SERV_AMB............5
    SERV_AMB............6
    SERV_AMB............7
    SERV_AMB............8

    SERV_FUN............10
    SERV_FUN............11
    SERV_FUN............12
    SERV_FUN............13
    SERV_FUN............14
    SERV_FUN............15
    SERV_FUN............16
    SERV_FUN............17

    SERV_MOT............18
    SERV_MOT............19
    SERV_MOT............20
    ................................
    ................................
    SERV_MOT............46

    I'd like to get the count of POINT NOT NULL.

    I tried this query:

    SELECT MACROS, AREA_T, count(POINT) count_point_not_null
    FROM TAB1 A, TAB2 B, TAB3 c
    WHERE A.USER_ID=B.USER_ID
    And A.ANSWER=C.ANSWER
    GROUP BY MACROS, AREA_T


    MACROS...........AREA_T..............count_point_n ot_null
    CRM...............SUD.......................159
    CRM...............NORD......................343
    CRM...............CENTRO....................523
    SERV_AMB..........SUD.......................1035
    SERV_AMB..........NORD......................2342
    SERV_AMB..........CENTRO....................3541
    SERV_FUN..........SUD........................606
    SERV_FUN..........NORD.......................1440
    SERV_FUN..........CENTRO.....................2124
    SERV_MOT..........SUD........................128
    SERV_MOT..........NORD.......................271
    SERV_MOT..........CENTRO.....................672


    But I get incorrect value for count_point_not_null, max value must be 1090 (25070/23).
    The only correct value is for MACROS = CRM (1025 point not null) because I've just one CRM for answer.

    How Can I write this query to get corret count of POINT?

    Thanks in advance!

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    If you change your query to

    Code:
    SELECT A.USER_ID, MACROS, AREA_T, count(POINT) count_point_not_null
    FROM TAB1 A, TAB2 B, TAB3 c
    WHERE A.USER_ID=B.USER_ID
    And A.ANSWER=C.ANSWER
    GROUP BY A.USER_ID, MACROS, AREA_T
    Do you get what you'd expect?

Posting Permissions

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