Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: Select column, column, count (*) group by column, column?

    At this point I'm self taught when it comes to CR. I can't say that I've taught myself much but after a week I'm getting the hang of it (been reading CR for dummies ) and have successfully created a few reports. Anyway, I simply want to look at two columns in a table, group the likes and count the results:

    select famnm, appnm, count (*)
    from relation
    where (clusterid = 'DEFAULT' or clusterid = 'CANADA')
    and famnm <> 'RUBY'
    group by famnm, appnm
    order by famnm, appnm

    End result looking something like this:

    FAMNM | APPNM | COUNT(*)
    Omni-3200 | H32413G | 1813
    Omni-3200 | H32P17R | 10557
    Omni-3200 | H32P18R | 365
    Omni-3200 | VSPR45M | 54521
    Omni-3200 | VSPR46C | 5549
    Omni-3200 | VSPR49B | 3671
    Omni-3210 | VSPR45M | 710
    Omni-3210 | VSPR46C | 109
    Omni-3300 | 11AO-OS | 3
    Omni-3300 | 14AO-OS | 57
    Omni-3300 | 16AO-OS | 25
    Omni-3300 | AMM | 27
    Omni-3300 | UEPA150M | 797
    Omni-3300 | UEPA260 | 3224
    Omni-3300 | UEPA281 | 48
    Omni-3300 | VMAC142 | 3012
    Omni-3300 | VMAC144 | 48
    Omni-3350 | 12BO-OS | 2
    Omni-3350 | UEPA260 | 22
    Omni-3350 | UEPA281 | 7
    Omni-3350 | VMAC142 | 22
    Omni-3350 | VMAC144 | 7
    Omni-3730 | 4A4-OS | 2
    Omni-3730 | SEPA281 | 115
    Omni-3730 | VMAC1425 | 109
    Omni-3730LE | 4A4-OS | 11
    Omni-3730LE | SEPA281 | 699
    Omni-3740 | 16A6-OS | 10
    Omni-3740 | 18AO-OS | 18
    Omni-3740 | UEPA260 | 3042
    Omni-3740 | UEPA281 | 233
    Omni-3740 | VIVOTECH | 2
    Omni-3740 | VMAC142 | 3028
    Omni-3740 | VMAC144 | 233
    Omni-3750 | 16A6-OS | 123
    Omni-3750 | 18AO-OS | 56
    Omni-3750 | CAN18AO-OS | 2


    Getting the list is easy but getting them grouped and counted is the issue. I end up with a list of all the above but not grouped and with a count of one (1) for each.
    I know this is simple but I'm still walking half blind. Any help would be appreciated.

  2. #2
    Join Date
    Jul 2005
    Posts
    50
    Using the Wizard, place FamNm and AppNm in the report.

    Group both fields and create a Summary (type = Count) for each field. (This will create header and footer for each.)

    Hide the Detail and the Group Footer 1 (FamNm) and Group Header 2 (AppNm).


    This will leave FamNm value in Group Header 1 as your Grouped FamNm's and AppNam will have mutlple footers under the FamNam (appearing as if it were a Detail), with a count right beside it.

    Move the Group Footer 2 AppNm field over to the right so it makes sense.

    Format from there...

  3. #3
    Join Date
    Apr 2006
    Posts
    5
    THANK YOU for replying!!

    I finally got it figured out late yesterday. This stuff is so tough at times. But when the answer is presented, it seems so easy.

Posting Permissions

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