Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: help on count and group

    Hi everybody I have this sql code

    SELECT i.infid, i.infname, i.infcalled, p.pubinfid, p.pubpub, p.publang, p.pubcount, p.pubid, n.cdpldesc
    FROM info AS i INNER JOIN
    pubssubscribe AS p ON i.infid = p.pubinfid INNER JOIN
    newpubs AS n ON p.pubid = n.pubid
    WHERE (i.infcond IS NULL)AND (p.pubid BETWEEN 30 AND 33) AND (p.pubcount > 1) AND (NOT (p.publang = '.'))

    there are many records where infid appears more than once because people could subscribe in different in different publicatons.
    ex.

    infid pubid
    1 30
    1 32
    1 33
    2 30

    etc... I want to count the infid appearing once and group it with infid

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    From your four records - what would the result look like?

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    Quote Originally Posted by pootle flump
    From your four records - what would the result look like?
    the result should be

    infid infcount pubid

    1 3 30
    1 32
    1 33
    2 1 30


    the infcount column should count the identical infid and if possible to group the identical infid to show only one time with together with the infcount


    thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    USE tempdb
    GO
    
    DECLARE    @t TABLE 
        (
            infid        TINYINT 
            , pubid        TINYINT
        )
    
    INSERT INTO @t
                SELECT    1, 30
    UNION ALL    SELECT    1, 32
    UNION ALL    SELECT    1, 33
    UNION ALL    SELECT    2, 30
    
    SELECT    infid
            , infcount    =    COUNT(*)    OVER    (PARTITION BY    infid)
            , pubid
    FROM    @t

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - I've just reread your required result. Inhibiting the result for "subsequent" rows is a PITA. Just get your report to do that.

  6. #6
    Join Date
    Aug 2006
    Posts
    87
    thanks try to do that

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Who? Me or you?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I decided to have a go since this is syntax I haven't tried before. I still reckon a report is better suited to it though.
    Code:
    SELECT    infid
            , infcount        =    CASE
                                    WHEN pubid = MIN(pubid)    OVER    (PARTITION BY    infid) THEN
                                        COUNT(*)    OVER    (PARTITION BY    infid)
                                END
            , pubid
    FROM    @t

Posting Permissions

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