Results 1 to 6 of 6

Thread: [RESOLVED] count distinct values why so hard?

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Resolved [RESOLVED] count distinct values why so hard?

    Hi.
    So after some grouping I end up with a CTE that contains some values like:

    club_name,member_cardNumber,member_firstName,member_lastName

    What I want to do is an extra column with cards that are unique, to be counted as 1 .
    So
    ClubX,123,AB,AB
    ClubX,123,AB,AB
    ClubX,124,AB,AB
    ClubX,123,ABD,ABD
    ClubX,125,ABC,ABA

    will have a total_members count of 3 and will look like this:

    3,ClubX,123,AB,AB
    3,ClubX,123,AB,AB
    3,ClubX,124,AB,AB
    3,ClubX,123,ABD,ABD
    3,ClubX,125,ABC,ABA

    I've been searching for hours for a non CTE solution so since I did not find anything I inserted to a CTE and posted here so I won't get some select over select over select that I would not use.
    And again googling (more like ducking) did not find anything. This amazes me as the solution seems so simple like:

    Code:
    select (select count (distinct member_cardNumber )  from CTE having count (member_cardNumber) > 1) as total_members
    ,club_name,member_cardNumber,member_firstName,member_lastName from CTE
    order by member_cardNumber
    Is what I wrote correct?

    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

    Re: count distinct values why so hard?

    So what's your counting criterium? member_CardNumber?
    and i still don't get what you want as a result

    and your sample-data is way confusing:
    cardnumber 123 has different first/last names? how is that supoosed to work?

    EDIT: At a guess:
    Code:
    WITH CTE1 AS (SELECT member_cardNumber, ROW_NUMBER() OVER(PARTITION BY member_cardNumber)  As CardCount FROM Club),
         CTE2 AS (SELECT member_cardNumber, MAX(CardCount) As CountMax FROM CTE1 GROUP BY member_cardNumber)
    SELECT c.club_name, c.member_cardNumber, c.member_firstName, c.member_lastName, c1.CountMax 
    FROM club As c 
    INNER JOIN CTE2 As C1
    ON C1.member_cardNumber=c.member_cardNumber
    Returns:
    club_name member_cardNumber member_firstName member_lastName CountMax
    ClubX 123 AB AB 3
    ClubX 123 AB AB 3
    ClubX 124 AB AB 1
    ClubX 123 ABD ABD 3
    ClubX 125 ABC ABA 1
    Last edited by Zvoni; Feb 20th, 2023 at 11:01 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: count distinct values why so hard?

    No the result is what I wrote 3,3,3,3,3 first names last name can differ if someone changed them on the card but don't worry about it.
    From a glance what I wrote with distinct seems to work but I need a verification or if there is another way but that seems the simpler.
    A user with a card will only be counted once so in my example there a 3 different cards reference more than once (or once) so even if a use had 100 rows with the same card, it would still count as one.

    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: count distinct values why so hard?

    One more question here.
    If I put this to a view then the CTE count -- select count (distinct member_cardNumber ) will give out all the member. Is there an option to fix this in a view or I must use the CTE outside of the view?
    Thanks.

    Edit. While wrote this I was also fixing the query so currently I use a CTE outside of the view that works as expected but out of curiosity if anyone would contribute...
    Last edited by sapator; Feb 21st, 2023 at 05:19 AM.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,419

    Re: count distinct values why so hard?

    A View is basically like a base-Table: You can only filter from the outside
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: count distinct values why so hard?

    OK, 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
  •  



Click Here to Expand Forum to Full Width