Counts on Subquerys

OK I have a query that bring back a unique identifier and text value
for instance

Select Distinct global_id, Page_url from PageList

global_id page_url
---- ----------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Now I want to do a count of the page_url.
Can this be done in one SQL statement using a sub query of some kindSELECT COUNT(DISTINCT Page_url) from PageList

??|||Originally posted by Brett Kaiser
SELECT COUNT(DISTINCT Page_url) from PageList

??

Brett, this doesn't seem to accomplich what I'm looking for. It's only bringing a count back of one for answerthis.htm, when there are clearly three records.

any suggestions|||I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult

:D

Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

But how about another shot in the dark

Select global_id, Page_url, COUNT(*)
from PageList
GROUP BY global_id, Page_url

And yes you clearly have 3, ummmm, rows...

global_id page_url
---- ----------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Isn't the 1st row of your result set kind of ironic?|||Originally posted by Brett Kaiser
I'm doing the best I can...but with the serial port to my telepathy device being blocked it's kind of difficult

:D

Maybe if you show us what the result you're looking for is suppose to be...DDl and sample raw data qould be very helpful to...

But how about another shot in the dark

Select global_id, Page_url, COUNT(*)
from PageList
GROUP BY global_id, Page_url

And yes you clearly have 3, ummmm, rows...

The problem I'm having is that the unique global_id is breaking out each record on the group by. so when it is added i can't get a count of the page_url

Isn't the 1st row of your result set kind of ironic?|||Well was that it?|||How about this:

SELECT page_url, count(page_url)
FROM pagelist
group by page_url

Counts in Date Ranges

Hi,
I have table with the following structure
User Name, Name of Product, Date of Purchase
I want to find out the number of times a particular product was purchased by
anybody in the last 30, 60 and 90 days.
Is there any function in SQL that will give this type of distribution? What
will be the best way of writing the query?
Thanks in anticipation,
Nitin MNitim
SELECT COUNT(ProdunctName) FROM
TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
<GETDATE()
AND ProductName='Something'
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have table with the following structure
> User Name, Name of Product, Date of Purchase
> I want to find out the number of times a particular product was purchased
by
> anybody in the last 30, 60 and 90 days.
> Is there any function in SQL that will give this type of distribution?
What
> will be the best way of writing the query?
> Thanks in anticipation,
> Nitin M
>|||Thanks Uri,
This is the solution to get the purchase count for a given time period.
However I was looking for a solution that gives me the purchase counts for
three time periods in one query. I am looking for a result set that looks
like
Product PurchaseInLast30Days PurchaseInLast60Days
PurchaseInLast90Days
----
--
A 10 13
31
B 0 5
5
and so on ...
I have a query that looks like... but it amounts to scanning the table 4
times. Is there a better way out?
select
P.ProductName,
Last30Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
Last60Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
Last90Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
from
Products P
group by
P.ProductName
Thanks,
Nitin M
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
> Nitim
> SELECT COUNT(ProdunctName) FROM
> TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
> <GETDATE()
> AND ProductName='Something'
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> by
> What
>|||I see what you mean
select
ProductName,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
Last30Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
Last60Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
Last90Days
from
Products
group by
ProductName
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri,
> This is the solution to get the purchase count for a given time period.
> However I was looking for a solution that gives me the purchase counts for
> three time periods in one query. I am looking for a result set that looks
> like
> Product PurchaseInLast30Days PurchaseInLast60Days
> PurchaseInLast90Days
> ----
--
> A 10 13
> 31
> B 0 5
> 5
> and so on ...
> I have a query that looks like... but it amounts to scanning the table 4
> times. Is there a better way out?
> select
> P.ProductName,
> Last30Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
> Last60Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
> Last90Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
> from
> Products P
> group by
> P.ProductName
> Thanks,
> Nitin M
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
purchased
>|||Thanks a lot Uri ... I got it ... It helped me.
Thank You
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23dOdsRzPFHA.1096@.TK2MSFTNGP12.phx.gbl...
>I see what you mean
> select
> ProductName,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
> Last30Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
> Last60Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
> Last90Days
> from
> Products
> group by
> ProductName
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> --
> purchased
>

Counts by groups

I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.

1select Count(*)as cnt--, TeamID2from EvalAnswers3where CoID=@.CoID4and EvaluatorID=@.EvaluatorID5and (Scr0=0 and Sugg0is NULL)6--group by TeamID7

BBradshaw:

even if none of the records satisfies the where clause.

- Why have the where clause then? A "Where" clause (as you know) filters out anything that doesn't match it... so what do you really want.. please explain what you are trying to query in human terms, not code terms, and I can help you better.

Thanks,

|||

Obviously, as shown by the commented-out query, SQL is designed to return a count of zero to tell me none of a specific filtered/matching kind of record currently exists, which is exactly what I want. I now want to show an itemized listing of all teams within a given company with their counts of existing filtered/matching records for each,even/especially if none exists. I know I can use the ALL construct on my Group By clause to get the zero counts for non-matching records, but that seems to give me too much (all companies, not just the one specified).

So, this morning I plan to play with the ALL and HAVING clauses, and expect to get what I need that way. However, I may be going about this all wrong, so any help would be appreciated.

|||

Please let me know if you see anything wrong with this code. It seems to work correctly for what I want, but I've tested in on only one scenario. I think the ALL and HAVING worked, rearranging the other parameters.

I appreciate your comments and help.

1selectCoID, TeamID, Count(*)as incomplete2fromEvalAnswers3whereScr0=0 and Sugg0is NULL4group byallCoID, TeamID, EvaluatorID5 havingCoID=@.CoID and EvaluatorID=@.EvaluatorID

|||I'm glad to see you've found the right way, you really need GROUP BY ALL in this case. This is why we need "GROUP BY ALL", it's by design, not a flaw of T-SQL.

Counter column in the result of SELECT statement

Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
AminSee if this helps:
http://support.microsoft.com/defaul...B;EN-US;q186133
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/defaul...B;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>

Counter column in the result of SELECT statement

Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin
See if this helps:
http://support.microsoft.com/default...;EN-US;q186133
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin
|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/default...;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>

Counter column in the result of SELECT statement

Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
AminSee if this helps:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
Hi,
I need to have an extra column in my SELECT's result that counts from 1 to
the end of result.
I think I can do it in reporting softwares such as crystal report, but I
want to do this in query analyzer with the SELECT statement, something like
this:
Counter CustomerID Country
--
1 ALFKI Germany
2 ANATR Mexico
3 ANTON Mexico
4 AROUT UK
5 BERGS Sweden
...
Thanks,
Amin|||Nice article! Thanks!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:#SkADeDOEHA.2876@.TK2MSFTNGP09.phx.gbl...
> See if this helps:
> http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:%23x4ocTDOEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to have an extra column in my SELECT's result that counts from 1 to
> the end of result.
> I think I can do it in reporting softwares such as crystal report, but I
> want to do this in query analyzer with the SELECT statement, something
like
> this:
> Counter CustomerID Country
> --
> 1 ALFKI Germany
> 2 ANATR Mexico
> 3 ANTON Mexico
> 4 AROUT UK
> 5 BERGS Sweden
> ...
>
> Thanks,
> Amin
>
>