Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

count number of rows per hour per client and in the end total Expand / Collapse
Author
Message
Posted Tuesday, September 16, 2008 10:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2008 5:34 PM
Points: 54, Visits: 221
how to count number of rows per hour per client and in the end total

here is something like needed output

datetime no. of rows client
9/16/2008 7:00:00 AM 10 abc
9/16/2008 8:00:00 AM 20 abc
9/16/2008 9:00:00 AM 30 abc
9/16/2008 7:00:00 AM 20 xyz
9/16/2008 8:00:00 AM 10 xyz
9/16/2008 9:00:00 AM 40 xyz
9/16/2008 7:00:00 AM 10 tqm
9/16/2008 8:00:00 AM 20 tqm
9/16/2008 9:00:00 AM 60 tqm
Total 220
Post #570438
Posted Tuesday, September 16, 2008 1:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,525, Visits: 4,071
You're looking for something like this?

Declare @StartTime	datetime,
@EndTime datetime,
@Total int

SELECT Client, datepart(hh, YourDate) HourNumber, SUM([NumRows]) VRowCount
INTO #1
FROM YourTable
WHERE YourDate BETWEEN @StartTime and @EndTime
GROUP BY Client, datepart(hh,YourDate)

SET @Total = (SELECT SUM(VRowCount)
FROM #1)

SELECT Client, HourNumber, VRowCount, @Total TotalRows
FROM #1



Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #570560
Posted Tuesday, September 16, 2008 1:43 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 814, Visits: 915
This will give you client sub-totals without the need for a temporary table.

SELECT 
CASE WHEN client IS NOT NULL AND hourSegment IS NULL THEN 'Client Total' ELSE CAST(hourSegment AS VARCHAR) END,
[No of Rows],
CASE WHEN client IS NULL AND hourSegment IS NULL THEN 'Grand Total' ELSE client END AS client
FROM (
SELECT LEFT(CONVERT(VARCHAR(30), [datetime], 20), 13) AS hourSegment, client, SUM([no. of rows]) AS [No of Rows]
FROM myTable
GROUP BY LEFT(CONVERT(VARCHAR(30), [datetime], 20), 13), client WITH ROLLUP
)x

The above should give you something like:

hourSegment No of Rows Client
9/16/2008 7 10 abc
9/16/2008 8 20 abc
9/16/2008 9 30 abc
Client Total 60 abc
9/16/2008 7 20 xyz
9/16/2008 8 10 xyz
9/16/2008 9 40 xyz
Client Total 70 xyz
9/16/2008 7 10 tqm
9/16/2008 8 20 tqm
9/16/2008 9 60 tqm
Client Total 90 tqm
220 Grand Total




Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #570576
Posted Tuesday, September 16, 2008 1:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2008 5:34 PM
Points: 54, Visits: 221
thanks , this wht i needed

Post #570581
Posted Friday, October 10, 2008 9:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2008 5:34 PM
Points: 54, Visits: 221
i don't want grant total row. can you help me in that?
Post #584071
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse