small count problem

[Lasse Edsvik]

Hello

I have this table

Player Points
A 11
B 11
C 8
D 1


and im trying to count number of "unique" Points greater than a value.

something like:

SELECT COUNT(*)+1 FROM Players WHERE Points>8

should return 2

SELECT COUNT(*)+1 FROM Players WHERE Points>1

should return 4

SELECT COUNT(*)+1 FROM Players WHERE Points>11

should return 1


Any ideas?

/Lasse

[Lasse Edsvik]

typed wrong:


SELECT COUNT(*)+1 FROM Players WHERE Points>1

should return 3

3, not 4



"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:u%23FkYPJ1DHA.2328@TK2MSFTNGP10.phx.gbl...

> Hello
>
> I have this table
>
> Player Points
> A 11
> B 11
> C 8
> D 1
>
>
> and im trying to count number of "unique" Points greater than a value.
>
> something like:
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>8
>
> should return 2
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>1
>
> should return 4
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>11
>
> should return 1
>
>
> Any ideas?
>
> /Lasse
>
>

[Ray at]

"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:u%23FkYPJ1DHA.2328@TK2MSFTNGP10.phx.gbl...

> Hello
>
> I have this table
>
> Player Points
> A 11
> B 11
> C 8
> D 1
>
>
> and im trying to count number of "unique" Points greater than a value.
>
> something like:
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>8
>
> should return 2

Shouldn't that only return 1 if you want the number of UNIQUE points greater
than your value? DO you want unique row counts or unique actual point
values?

>
> SELECT COUNT(*)+1 FROM Players WHERE Points>1
>
> should return 4

I see two unique point values or three unique rows here, not 4.

>
> SELECT COUNT(*)+1 FROM Players WHERE Points>11
>
> should return 1

I don't see anything greater than 11 here. What exactly are you after in
your queries?

Ray at work

[Ray at]

Please disregard my last post. I missed the "+1" part. (ashamed...)

Ray at work

"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:u%23FkYPJ1DHA.2328@TK2MSFTNGP10.phx.gbl...

> Hello
>
> I have this table
>
> Player Points
> A 11
> B 11
> C 8
> D 1
>
>
> and im trying to count number of "unique" Points greater than a value.
>
> something like:
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>8
>
> should return 2
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>1
>
> should return 4
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>11
>
> should return 1
>
>
> Any ideas?
>
> /Lasse
>
>

[Ray at]

select count(x) +1 from (select distinct points as x from players where
points > 8)
Returns: 2

select count(x) +1 from (select distinct points as x from players where
points > 1)
Returns: 3

select count(x) +1 from (select distinct points as x from players where
points > 11)
Returns: 1

Ray at work

"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:u%23FkYPJ1DHA.2328@TK2MSFTNGP10.phx.gbl...

> Hello
>
> I have this table
>
> Player Points
> A 11
> B 11
> C 8
> D 1
>
>
> and im trying to count number of "unique" Points greater than a value.
>
> something like:
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>8
>
> should return 2
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>1
>
> should return 4
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>11
>
> should return 1
>
>
> Any ideas?
>
> /Lasse
>
>

[Bob Barrows]

Lasse Edsvik wrote:

> Hello
>
> I have this table
>
> Player Points
> A 11
> B 11
> C 8
> D 1
>
>
> and im trying to count number of "unique" Points greater than a value.
>
> something like:
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>8
>
> should return 2
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>1
>
> should return 4
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>11
>
> should return 1
>
>
> Any ideas?
>
> /Lasse

I remember from your last post that you were using Access. Is that still the
case? Always tell us the database and version please. For now, I'm going to
assume that it's still Access - I hope I am not wasting my time here.

Anyways, if you WERE using SQL Server, you would be in luck, because T-SQL's
COUNT function allows the use of the DISTINCT keyword, as in:
SELECT COUNT(DISTINCT Points) ...

Unfortunately, this is not supported in JetSQL. What you are going to need
to do is generate a separate virtual table containing the distinct points.

Create a query using this SQL:
SELECT DISTINCT Points FROM Players
Save it as qDistinctPoints

Then create a new query with this SQL:
SELECT COUNT(*)+1 FROM qDistinctPoints WHERE Points>8

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Lasse Edsvik]

thank you guys

/Lasse

"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:u%23FkYPJ1DHA.2328@TK2MSFTNGP10.phx.gbl...

> Hello
>
> I have this table
>
> Player Points
> A 11
> B 11
> C 8
> D 1
>
>
> and im trying to count number of "unique" Points greater than a value.
>
> something like:
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>8
>
> should return 2
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>1
>
> should return 4
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>11
>
> should return 1
>
>
> Any ideas?
>
> /Lasse
>
>

[Aaron Bertrand [MVP]]

> Anyways, if you WERE using SQL Server, you would be in luck, because
T-SQL's

> COUNT function allows the use of the DISTINCT keyword, as in:
> SELECT COUNT(DISTINCT Points) ...

You could also use the GROUP BY and HAVING clause.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Bob Barrows]

Aaron Bertrand [MVP] wrote:

>> Anyways, if you WERE using SQL Server, you would be in luck, because
>> T-SQL's COUNT function allows the use of the DISTINCT keyword, as in:
>> SELECT COUNT(DISTINCT Points) ...

>
> You could also use the GROUP BY and HAVING clause.

I'm not sure how that would work in this case ... can you go into more
detail?

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand [MVP]]

Sorry, I think I may have misread the question. In many cases when I see a
SELECT (DISTINCT COUNT) it is usually a candidate for using GROUP BY. In
this case, I don't think DISTINCT adds any benefit, unless the question is
worded poorly.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ugh3MNK1DHA.2396@TK2MSFTNGP09.phx.gbl...

> Aaron Bertrand [MVP] wrote:

> >> Anyways, if you WERE using SQL Server, you would be in luck, because
> >> T-SQL's COUNT function allows the use of the DISTINCT keyword, as in:
> >> SELECT COUNT(DISTINCT Points) ...

> >
> > You could also use the GROUP BY and HAVING clause.

>
> I'm not sure how that would work in this case ... can you go into more
> detail?

[Chris Hohmann]

"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:u%23FkYPJ1DHA.2328@TK2MSFTNGP10.phx.gbl...

> Hello
>
> I have this table
>
> Player Points
> A 11
> B 11
> C 8
> D 1
>
>
> and im trying to count number of "unique" Points greater than a value.
>
> something like:
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>8
>
> should return 2
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>1
>
> should return 4
>
> SELECT COUNT(*)+1 FROM Players WHERE Points>11
>
> should return 1
>
>
> Any ideas?
>
> /Lasse
>
>

Assuming Players.Player is unique, here's an alternative to the
subquery-based solutions offered thus far.

PARAMETERS
prmPoints INT
;
SELECT
COUNT(*) + 1
FROM
Players AS P1 LEFT JOIN
Players AS P2 ON
P1.Points = P2.Points AND
P1.Player > P2.Player
WHERE
P2.Player IS NULL AND
P1.Points > prmPoints

HTH
-Chris Hohmann

[Lasse Edsvik]

Bob,

I cant use

SELECT COUNT(Distinct Points) FROM MyTable

Syntax error (missing operator) in query expression 'COUNT(DISTINCT
Points)'.


/Lasse


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ugh3MNK1DHA.2396@TK2MSFTNGP09.phx.gbl...

> Aaron Bertrand [MVP] wrote:

> >> Anyways, if you WERE using SQL Server, you would be in luck, because
> >> T-SQL's COUNT function allows the use of the DISTINCT keyword, as in:
> >> SELECT COUNT(DISTINCT Points) ...

> >
> > You could also use the GROUP BY and HAVING clause.

>
> I'm not sure how that would work in this case ... can you go into more
> detail?
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bob Barrows]

I did say that you could not use this in Access (JetSQL). You either have to
use my subquery solution:
Create a query using this SQL:
SELECT DISTINCT Points FROM Players
Save it as qDistinctPoints

Then create a new query with this SQL:
SELECT COUNT(*)+1 FROM qDistinctPoints WHERE Points>8

OR

SELECT COUNT(*)+1 FROM (
SELECT DISTINCT Points FROM Players WHERE Points>8) q

OR Chris's self outer join solution.

Bob Barrows

Lasse Edsvik wrote:

> Bob,
>
> I cant use
>
> SELECT COUNT(Distinct Points) FROM MyTable
>
> Syntax error (missing operator) in query expression 'COUNT(DISTINCT
> Points)'.
>
>
> /Lasse
>
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:ugh3MNK1DHA.2396@TK2MSFTNGP09.phx.gbl...

>> Aaron Bertrand [MVP] wrote:

>>>> Anyways, if you WERE using SQL Server, you would be in luck,
>>>> because T-SQL's COUNT function allows the use of the DISTINCT
>>>> keyword, as in: SELECT COUNT(DISTINCT Points) ...
>>>
>>> You could also use the GROUP BY and HAVING clause.

>>
>> I'm not sure how that would work in this case ... can you go into
>> more detail?
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.