-
Feb 20th, 2023, 10:16 AM
#1
[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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Feb 20th, 2023, 10:42 AM
#2
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
-
Feb 20th, 2023, 11:25 AM
#3
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Feb 21st, 2023, 04:58 AM
#4
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Feb 21st, 2023, 06:17 AM
#5
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
-
Feb 21st, 2023, 06:18 AM
#6
Re: count distinct values why so hard?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|