Counting problem

Hi everyone,

another problem:

I'm trying to count the number of rows but it's not working. Here's my code:

SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites]
FROM [Activities]
WHERE [Person ID] IN
(
 SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people)
 FROM [Activites]
)

As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out.

Any help?
AndrewHi,

i hope the following query will solve your issue.

select count(*) from activities group by personid

Regards
Ravi|||It's actually doing exactly what you are asking it to do: count the total number of rows in activities where personid exists inthe activities table.

What you REALLY want it to do is to count the DISTINCT people in that table.

Try:

SELECT 'TOTAL number of rows', count(DISTINCT PersonID)
FROM [Activities]

The GROUP BY solution works great, too...|||Thanks heaps for the feedback!!

Cheers
Andrew

Counting consecutive days

My specific problem:
I have a DB full of daily stock prices including the open, close, high,
and low. I want to A) select all stocks whose current day high is lower
then the previous day's high for at least 5 consecutive days and B)
count the number of days that stock has displayed this trend.
I can do A. I created a UDF that returns all stocks that display this
trend for at least 5 days. I am having problems with B. I've been
trying to write to different queries and then try to combine them but
I'm having some problems.
A)
========================================
=================================
/* returns list of stocks that fit the 5 day HIGH */
CREATE FUNCTION dbo.udf_List5DayHigh(@.date1 DATETIME, @.date2
DATETIME,@.date3 DATETIME,@.date4 DATETIME,@.date5 DATETIME,@.date6 DATETIME )
RETURNS TABLE
AS
RETURN
select e.equityID, e.tickerRealtick, p1.high from equities e, prices
p1, prices p2, prices p3, prices p4, prices p5, prices p6
where e.equityID = p1.equityID and e.equityID = p2.equityID and
e.equityID = p3.equityID and e.equityID = p4.equityID and e.equityID =
p5.equityID
and e.equityID = p6.equityID
and p1.priceDate = @.date1 and p2.priceDate = @.date2 and p3.priceDate =
@.date3 and
p4.priceDate = @.date4 and p5.priceDate = @.date5 and p6.priceDate =@.date6
and p1.high < p2.high and p2.high < p3.high and p3.high < p4.high and
p4.high < p5.high and p5.high < p6.high
========================================
=================================
Table Descriptions:
Equities
--
equityID (PK)
tickerRealtick
Prices
--
priceID (PK)
equityID
priceDate
high
low
close
open
==============
select e.tickerRealTick, p.priceDate, p.high,p2.priceDate, p2.high
from prices p, prices p2, equities e
where e.equityID = 3783 and p.equityID = e.equityID and p2.equityID =
e.equityID and p.priceDate = dateadd(d, case when
datepart(dw,p.PriceDate) = 2 then -3
else -1 end, p2.priceDate)
and p.high > p2.high and p.priceDate = '3/1/05'
order by p.priceDate desc
====================
I think couting should be something like this.
General problem others might be more familiar with:
You store total sales for a retail store in a SQL server. You want to
count the number of consecutive days that sales are up?Won,
See if this is a helpful start.
create table T (
cust int,
dte smalldatetime,
sale money,
primary key (cust, dte)
)
insert into T
select EmployeeID, cast('20050101' as datetime) + ProductID,
abs(binary_checksum(newid())%10000)/$100 + 2*(100+ProductID)
from Northwind..Products, Northwind..Employees
go
create view UpsAndDowns as
select Tnow.cust, Tnow.dte, sign(Tnow.sale - Tyest.sale) as trend
from T as Tnow, T as Tyest
where Tnow.dte = Tyest.dte + 1
and Tnow.cust = Tyest.cust
go
select cust, trendStart, trendEnd, trendLen
from (
select V1.cust, V1.dte - 1 as trendStart, V2.dte as trendEnd,
1+datediff(day,V1.dte,V2.dte) as trendLen
from UpsAndDowns as V1, UpsAndDowns as V2
where V2.cust = V1.cust
and V2.trend = 1
and V2.trend <> -V1.trend
and V2.dte >= V1.dte
and not exists (
select * from UpsAndDowns as V3
where V3.cust = V1.cust
and V3.dte > V1.dte
and V3.dte < V2.dte
and V3.trend = -V1.trend
)
) T
order by cust, trendStart, trendEnd
go
drop view UpsAndDowns
drop table T
Steve Kass
Drew University
Won Lee wrote:

> My specific problem:
> I have a DB full of daily stock prices including the open, close,
> high, and low. I want to A) select all stocks whose current day high
> is lower then the previous day's high for at least 5 consecutive days
> and B) count the number of days that stock has displayed this trend.
> I can do A. I created a UDF that returns all stocks that display this
> trend for at least 5 days. I am having problems with B. I've been
> trying to write to different queries and then try to combine them but
> I'm having some problems.
> A)
> ========================================
=================================
> /* returns list of stocks that fit the 5 day HIGH */
> CREATE FUNCTION dbo.udf_List5DayHigh(@.date1 DATETIME, @.date2
> DATETIME,@.date3 DATETIME,@.date4 DATETIME,@.date5 DATETIME,@.date6
> DATETIME )
> RETURNS TABLE
> AS
> RETURN
> select e.equityID, e.tickerRealtick, p1.high from equities e, prices
> p1, prices p2, prices p3, prices p4, prices p5, prices p6
> where e.equityID = p1.equityID and e.equityID = p2.equityID and
> e.equityID = p3.equityID and e.equityID = p4.equityID and e.equityID =
> p5.equityID
> and e.equityID = p6.equityID
> and p1.priceDate = @.date1 and p2.priceDate = @.date2 and p3.priceDate =
> @.date3 and
> p4.priceDate = @.date4 and p5.priceDate = @.date5 and p6.priceDate
> =@.date6
> and p1.high < p2.high and p2.high < p3.high and p3.high < p4.high and
> p4.high < p5.high and p5.high < p6.high
> ========================================
=================================
> Table Descriptions:
>
> Equities
> --
> equityID (PK)
> tickerRealtick
> Prices
> --
> priceID (PK)
> equityID
> priceDate
> high
> low
> close
> open
> ==============
> select e.tickerRealTick, p.priceDate, p.high,p2.priceDate, p2.high
> from prices p, prices p2, equities e
> where e.equityID = 3783 and p.equityID = e.equityID and p2.equityID =
> e.equityID and p.priceDate = dateadd(d, case when
> datepart(dw,p.PriceDate) = 2 then -3
> else -1 end, p2.priceDate)
> and p.high > p2.high and p.priceDate = '3/1/05'
> order by p.priceDate desc
> ====================
> I think couting should be something like this.
>
>
> General problem others might be more familiar with:
> You store total sales for a retail store in a SQL server. You want to
> count the number of consecutive days that sales are up?|||Steve Kass wrote:
> Won,
> See if this is a helpful start.
> create table T (
> cust int,
> dte smalldatetime,
> sale money,
> primary key (cust, dte)
> )
> insert into T
> select EmployeeID, cast('20050101' as datetime) + ProductID,
> abs(binary_checksum(newid())%10000)/$100 + 2*(100+ProductID)
> from Northwind..Products, Northwind..Employees
> go
> create view UpsAndDowns as
> select Tnow.cust, Tnow.dte, sign(Tnow.sale - Tyest.sale) as trend
> from T as Tnow, T as Tyest
> where Tnow.dte = Tyest.dte + 1
> and Tnow.cust = Tyest.cust
> go
> select cust, trendStart, trendEnd, trendLen
> from (
> select V1.cust, V1.dte - 1 as trendStart, V2.dte as trendEnd,
> 1+datediff(day,V1.dte,V2.dte) as trendLen
> from UpsAndDowns as V1, UpsAndDowns as V2
> where V2.cust = V1.cust
> and V2.trend = 1
> and V2.trend <> -V1.trend
> and V2.dte >= V1.dte
> and not exists (
> select * from UpsAndDowns as V3
> where V3.cust = V1.cust
> and V3.dte > V1.dte
> and V3.dte < V2.dte
> and V3.trend = -V1.trend
> )
> ) T
> order by cust, trendStart, trendEnd
> go
> drop view UpsAndDowns
> drop table T
>
> Steve Kass
> Drew University
Steve,
Thanks will take a look at that.|||"Won Lee" <nospam@.nospam.com> wrote in message
news:%23pN4HO0HFHA.2620@.tk2msftngp13.phx.gbl...
> My specific problem:
> I have a DB full of daily stock prices including the open, close, high, an
d
> low. I want to A) select all stocks whose current day high is lower then
the
> previous day's high for at least 5 consecutive days and B) count the numbe
r of
> days that stock has displayed this trend.
> I can do A. I created a UDF that returns all stocks that display this tre
nd
> for at least 5 days. I am having problems with B. I've been trying to wr
ite
> to different queries and then try to combine them but I'm having some
> problems.
> A)
> ========================================
=================================
> /* returns list of stocks that fit the 5 day HIGH */
> CREATE FUNCTION dbo.udf_List5DayHigh(@.date1 DATETIME, @.date2 DATETIME,@.dat
e3
> DATETIME,@.date4 DATETIME,@.date5 DATETIME,@.date6 DATETIME )
> RETURNS TABLE
> AS
> RETURN
> select e.equityID, e.tickerRealtick, p1.high from equities e, prices p1,
> prices p2, prices p3, prices p4, prices p5, prices p6
> where e.equityID = p1.equityID and e.equityID = p2.equityID and e.equityID
=
> p3.equityID and e.equityID = p4.equityID and e.equityID = p5.equityID
> and e.equityID = p6.equityID
> and p1.priceDate = @.date1 and p2.priceDate = @.date2 and p3.priceDate = @.da
te3
> and
> p4.priceDate = @.date4 and p5.priceDate = @.date5 and p6.priceDate =@.date6
> and p1.high < p2.high and p2.high < p3.high and p3.high < p4.high and p4.h
igh
> < p5.high and p5.high < p6.high
> ========================================
=================================
> Table Descriptions:
>
> Equities
> --
> equityID (PK)
> tickerRealtick
> Prices
> --
> priceID (PK)
> equityID
> priceDate
> high
> low
> close
> open
> ==============
> select e.tickerRealTick, p.priceDate, p.high,p2.priceDate, p2.high
> from prices p, prices p2, equities e
> where e.equityID = 3783 and p.equityID = e.equityID and p2.equityID =
> e.equityID and p.priceDate = dateadd(d, case when datepart(dw,p.PriceDate)
= 2
> then -3
> else -1 end, p2.priceDate)
> and p.high > p2.high and p.priceDate = '3/1/05'
> order by p.priceDate desc
> ====================
> I think couting should be something like this.
>
>
> General problem others might be more familiar with:
> You store total sales for a retail store in a SQL server. You want to cou
nt
> the number of consecutive days that sales are up?
CREATE TABLE Prices
(
ticker VARCHAR(5) NOT NULL,
priceDate DATETIME NOT NULL,
highPrice DECIMAL (8,2) NOT NULL CHECK (highPrice > 0),
PRIMARY KEY (ticker, priceDate)
)
-- 1-day trends, that is, how price moves between consecutive trading
-- days for each stock
-- Direction of +1 is an uptrend, -1 is a downtrend, and 0 is no change
-- Note that stock price dates are not necessarily consecutive days
-- because of wends, holidays, unplanned closings, suspension
-- of trading of a particular issue, or simply missing data
CREATE VIEW PriceTrends1Day (ticker, priceDate, direction)
AS
SELECT P2.ticker, P2.priceDate, SIGN(P2.highPrice - P1.highPrice)
FROM Prices AS P1
INNER JOIN
Prices AS P2
ON P1.ticker = P2.ticker AND
P1.priceDate =
(SELECT MAX(priceDate)
FROM Prices
WHERE ticker = P2.ticker AND
priceDate < P2.priceDate)
-- startDate is inclusive for a trend and endDate is exclusive, i.e.,
-- [startDate, endDate)
-- An endDate of NULL indicates trend is current
-- nDays is the number of days of trend
-- Note that if IBM on 3 consecutive trading days is 100, 101, and 102
-- then this is a 2-day uptrend
CREATE VIEW PriceTrends (ticker, startDate, endDate, direction, nDays)
AS
SELECT T.ticker, T.startDate, T.endDate, T.direction, COUNT(*)
FROM (SELECT ticker,
MIN(priceDate), newDirectionDate,
direction
FROM (SELECT PT1.ticker,
PT1.priceDate,
MIN(PT2.priceDate),
PT1.direction
FROM PriceTrends1Day AS PT1
LEFT OUTER JOIN
PriceTrends1Day AS PT2
ON
PT1.ticker = PT2.ticker AND
PT2.priceDate > PT1.priceDate AND
PT2.direction <> PT1.direction
GROUP BY PT1.ticker, PT1.priceDate, PT1.direction)
AS
CT(ticker, priceDate, newDirectionDate, direction)
GROUP BY ticker, newDirectionDate, direction) AS
T(ticker, startDate, endDate, direction)
INNER JOIN
Prices AS P
ON P.ticker = T.ticker AND
P.priceDate >= T.startDate AND
P.priceDate < COALESCE(T.endDate, '99991231')
GROUP BY T.ticker, T.startDate, T.endDate, T.direction
-- All stocks and dates with a downtrend of at least 5 days
SELECT ticker, startDate, endDate, direction, nDays
FROM PriceTrends
WHERE direction = -1 AND nDays >= 5
JAG|||>
> CREATE TABLE Prices
> (
> ticker VARCHAR(5) NOT NULL,
> priceDate DATETIME NOT NULL,
> highPrice DECIMAL (8,2) NOT NULL CHECK (highPrice > 0),
> PRIMARY KEY (ticker, priceDate)
> )
> -- 1-day trends, that is, how price moves between consecutive trading
> -- days for each stock
> -- Direction of +1 is an uptrend, -1 is a downtrend, and 0 is no change
> -- Note that stock price dates are not necessarily consecutive days
> -- because of wends, holidays, unplanned closings, suspension
> -- of trading of a particular issue, or simply missing data
> CREATE VIEW PriceTrends1Day (ticker, priceDate, direction)
> AS
> SELECT P2.ticker, P2.priceDate, SIGN(P2.highPrice - P1.highPrice)
> FROM Prices AS P1
> INNER JOIN
> Prices AS P2
> ON P1.ticker = P2.ticker AND
> P1.priceDate =
> (SELECT MAX(priceDate)
> FROM Prices
> WHERE ticker = P2.ticker AND
> priceDate < P2.priceDate)
> -- startDate is inclusive for a trend and endDate is exclusive, i.e.,
> -- [startDate, endDate)
> -- An endDate of NULL indicates trend is current
> -- nDays is the number of days of trend
> -- Note that if IBM on 3 consecutive trading days is 100, 101, and 102
> -- then this is a 2-day uptrend
> CREATE VIEW PriceTrends (ticker, startDate, endDate, direction, nDays)
> AS
> SELECT T.ticker, T.startDate, T.endDate, T.direction, COUNT(*)
> FROM (SELECT ticker,
> MIN(priceDate), newDirectionDate,
> direction
> FROM (SELECT PT1.ticker,
> PT1.priceDate,
> MIN(PT2.priceDate),
> PT1.direction
> FROM PriceTrends1Day AS PT1
> LEFT OUTER JOIN
> PriceTrends1Day AS PT2
> ON
> PT1.ticker = PT2.ticker AND
> PT2.priceDate > PT1.priceDate
AND
> PT2.direction <> PT1.direction
> GROUP BY PT1.ticker, PT1.priceDate, PT1.direc
tion)
> AS
> CT(ticker, priceDate, newDirectionDate, direct
ion)
> GROUP BY ticker, newDirectionDate, direction) AS
> T(ticker, startDate, endDate, direction)
> INNER JOIN
> Prices AS P
> ON P.ticker = T.ticker AND
> P.priceDate >= T.startDate AND
> P.priceDate < COALESCE(T.endDate, '99991231')
> GROUP BY T.ticker, T.startDate, T.endDate, T.direction
> -- All stocks and dates with a downtrend of at least 5 days
> SELECT ticker, startDate, endDate, direction, nDays
> FROM PriceTrends
> WHERE direction = -1 AND nDays >= 5
> --
> JAG
John,
Thanks for post. I am going to read over it carefully and see how it
works out.|||John Gilson wrote:
> CREATE TABLE Prices
> (
> ticker VARCHAR(5) NOT NULL,
> priceDate DATETIME NOT NULL,
> highPrice DECIMAL (8,2) NOT NULL CHECK (highPrice > 0),
> PRIMARY KEY (ticker, priceDate)
> )
> -- 1-day trends, that is, how price moves between consecutive trading
> -- days for each stock
> -- Direction of +1 is an uptrend, -1 is a downtrend, and 0 is no change
> -- Note that stock price dates are not necessarily consecutive days
> -- because of wends, holidays, unplanned closings, suspension
> -- of trading of a particular issue, or simply missing data
> CREATE VIEW PriceTrends1Day (ticker, priceDate, direction)
> AS
> SELECT P2.ticker, P2.priceDate, SIGN(P2.highPrice - P1.highPrice)
> FROM Prices AS P1
> INNER JOIN
> Prices AS P2
> ON P1.ticker = P2.ticker AND
> P1.priceDate =
> (SELECT MAX(priceDate)
> FROM Prices
> WHERE ticker = P2.ticker AND
> priceDate < P2.priceDate)
> -- startDate is inclusive for a trend and endDate is exclusive, i.e.,
> -- [startDate, endDate)
> -- An endDate of NULL indicates trend is current
> -- nDays is the number of days of trend
> -- Note that if IBM on 3 consecutive trading days is 100, 101, and 102
> -- then this is a 2-day uptrend
> CREATE VIEW PriceTrends (ticker, startDate, endDate, direction, nDays)
> AS
> SELECT T.ticker, T.startDate, T.endDate, T.direction, COUNT(*)
> FROM (SELECT ticker,
> MIN(priceDate), newDirectionDate,
> direction
> FROM (SELECT PT1.ticker,
> PT1.priceDate,
> MIN(PT2.priceDate),
> PT1.direction
> FROM PriceTrends1Day AS PT1
> LEFT OUTER JOIN
> PriceTrends1Day AS PT2
> ON
> PT1.ticker = PT2.ticker AND
> PT2.priceDate > PT1.priceDate
AND
> PT2.direction <> PT1.direction
> GROUP BY PT1.ticker, PT1.priceDate, PT1.direc
tion)
> AS
> CT(ticker, priceDate, newDirectionDate, direct
ion)
> GROUP BY ticker, newDirectionDate, direction) AS
> T(ticker, startDate, endDate, direction)
> INNER JOIN
> Prices AS P
> ON P.ticker = T.ticker AND
> P.priceDate >= T.startDate AND
> P.priceDate < COALESCE(T.endDate, '99991231')
> GROUP BY T.ticker, T.startDate, T.endDate, T.direction
> -- All stocks and dates with a downtrend of at least 5 days
> SELECT ticker, startDate, endDate, direction, nDays
> FROM PriceTrends
> WHERE direction = -1 AND nDays >= 5
> --
> JAG
>
John,
The first part of query works. I went through it to make sure I
understand. The second view returns an error.
SELECT equityID, startDate, endDate, direction, nDays
FROM PriceTrends
WHERE direction = -1 AND nDays >= 5
Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.|||> The first part of query works. I went through it to make sure I
> understand. The second view returns an error.
> SELECT equityID, startDate, endDate, direction, nDays
> FROM PriceTrends
> WHERE direction = -1 AND nDays >= 5
>
> Server: Msg 8624, Level 16, State 3, Line 1
> Internal SQL Server error.
Never mind. I googled the sql server error code and was
because the KB was about delete with not in. Then I returned to google
and looked a few lines down and it described my issue.
Thanks.