ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

strsql

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • strsql

    I have the following sql statement in strsql

    SELECT B.SPCODE,a.revyr,a.revmo,a.dispos
    FROM GNLIB/SFED201 A join GNLIB/SFAM B on ULSAM=LSAMS#

    ORDER BY b.spcode,a.revyr,a.revmo

    which gives me this

    com yer mo dis
    3006 2002 11 1
    3006 2002 11 5
    3006 2002 11 3
    3007 2002 5 1
    3007 2002 5 2
    3007 2002 6 1
    i want to count the dis field by month


    3006 2002 11 3
    3007 2002 5 2
    3007 2002 6 1

  • #2
    Code:
    select spcode, revyr, revmo, count(*) as count
      from (SELECT B.SPCODE,a.revyr,a.revmo,a.dispos
              FROM GNLIB/SFED201 A
              join GNLIB/SFAM B on ULSAM=LSAMS#) as x
     group by spcode, revyr, revmo
     order by spcode, revyr, revmo
    or

    Code:
    with temp as
     (SELECT B.SPCODE,a.revyr,a.revmo,a.dispos 
        FROM GNLIB/SFED201 A
        join GNLIB/SFAM B on ULSAM=LSAMS#)
    select spcode, revyr, revmo, count(*) as count
      from temp
     group by spcode, revyr, revmo
     order by spcode, revyr, revmo

    Comment


    • #3
      In this case, neither a nested sub-select nor a common table expression is needed, COUNT(Distinct Column) will do the job
      Try the following statement:

      Code:
      SELECT b.SpCode, a.revyr, a.revmo,
             Count(Distinct a.dispos)
         FROM GNLIB/SFED201 A join GNLIB/SFAM B on ULSAM=LSAMS#
         Group By b.SpCode, a.revyr, a.revmo
         ORDER BY b.spcode,a.revyr,a.revmo
      Birgitta

      Comment


      • #4
        Why, of course, Birgitta! That's even better! Why didn't I think of that?

        Comment


        • tomliotta
          tomliotta commented
          Editing a comment
          Are you really wanting an answer? :-)
      Working...
      X