Count numer of records per minute 1

[nelco]

The columns that I am interested in a table are:
Deal_No. Deal_Date
If I have to count the Deal_No. for every min(In Deal_Date), how can this be done?

 

[RiverGuy]

SELECT
CONVERT(VARCHAR(5), Deal_Date, 108) AS Deal_Minute,
COUNT(*) AS Ct
FROM
YourTable
GROUP BY CONVERT(VARCHAR(5), Deal_Date, 108)
ORDER BY CONVERT(VARCHAR(5), Deal_Date, 108)

 

[bborissov]

SELECT Deal_No,
       CONVERT(varchar(16),@Deal_Date,127),
       COUNT(*)
FROM YourTable
GROUP BY Deal_No,
         CONVERT(varchar(16),@Deal_Date,127)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[nelco]

Thanks Riverguy and Borislav

 

[RiverGuy]

You probably want to use Borislav's solution, for some reason, I was thinking the date was irrelavant.

 

[nelco]

I also want to get a cumulative count of deals for which I am trying to do a self join, but its not giving the right results yet.
I've captured the DealMinute and CountOfdeals in a temp table, from which I want to get teh cumulative count of deals.
Something like this is what I intend:

DealMinute CountOfDeals CumulativeCountOfDeals
04:54 38 38
04:55 38 76
04:56 125 201

 

[nelco]

Select		b1.Deal_Minute, b1.CountOfDeals, SUM(b1.CountOfDeals) AS CumulativeCountOfDeals
From		#B b1, #B b2
Where	b1.Deal_Minute > b2.Deal_Minute
Group By b1.Deal_Minute, b1.CountOfDeals
Order By b1.Deal_Minute

This is not working

 

[RiverGuy]

Well, you want >= for one, and you want to sum b2, not b1.

 

[bborissov]

Select b1.Deal_Minute,
       b1.CountOfDeals,
       SUM(b2.CountOfDeals) AS CumulativeCountOfDeals
From #B b1
INNER JOIN #B b2 ON b1.Deal_Minute <= b2.Deal_Minute
Group By b1.Deal_Minute,
         b1.CountOfDeals
Order By b1.Deal_Minute

not tested


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.