Counting Rows In A Group By...

Jul 26, 2000

Hi,

I'm using SQL Server 7.0. My problem is that I'd like to count how many rows are in a group by. For example, here is my data:

Agent Branch
1 1
2 1
1 1
2 1
1 2
1 2

If I do this:

Select Agent, Branch
From Table1
Group By Agent, Branch

I get:

Agent Branch
1 1
2 1
1 2

What I need to do is count the records (3) in the group by. I've tried a few things but I can't seem to come up with the number of rows in a group by.

Can anyone help me?

Thanks in advance,
Darrin

View 2 Replies

Counting Total Rows When Using GROUP BY

Oct 20, 2007

hi,
i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits
WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL)
GROUP BY UserName
UNION
SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits AS UserVisits_1
WHERE (ProductID = @ProductID) AND (UserName IS NULL)
GROUP BY AnonimIP
this will return something like:
zuperboy90 - 4 visits
ANONIMOUS - 6 visits
85.104.103 - 2 visits etc
how can i count the rows returned in both selections (4+6+2 = 12) ?
thank you

View 9 Replies View Related

Counting Inserts & Updates From A Specific Or Group Of Tables?

Mar 11, 2002

I am kinda new with SQL and am trying to get a count of on the number of updates and or inserts to any given or group of tables and cannot get the syntax correct...can anyone help with this?
Thank you in advance.
Colin P.

View 1 Replies View Related

Counting Rows

Oct 19, 2005

Is there a way to get a SQL Stored Procedure to count the number of rows returned and then store that total in a variable?

View 1 Replies View Related

Counting Rows

Aug 29, 2006

hi all

quick question

is there any way to set up a column that has the row count in it? i need this for a program i am developing and this would make it much easier to deal with. I know i can get a total count but when i run a count within a select statement i just get '1' for every row. thanks

tibor

View 1 Replies View Related

Counting Rows

Mar 10, 2004

Hi,

I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.

I figured I could add a column that contains that number. But how will I get it there?

UPDATE [_temp] T1
SET T1.SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T1.SERIAL = T2.SERIAL)

Doesn't work. Any ideas?

View 5 Replies View Related

Counting Rows With Value?

Oct 11, 2013

T-SQL counting rows with a value:

My query is like this:

SELECT
Name,
A,
B,
C,
D
FROM
Table
ORDER BY
Name

The query result looks like this:

Name A B C D
Bert 2 0 0 0
Bert 3 6 0 1
Mark 0 0 1 0
Mark 0 8 8 0
Mark 5 6 3 0

I want my result to count the cells with a value and group them on name Like this:

Name A B C D
Bert 2 1 0 1
Mark 3 2 3 0

COUNT() gives back every row from a name.

SUM() adds up the values

I have tried many things with sub queries and combined functions, but up until now with no results.

How do I do this ?

View 4 Replies View Related

Counting Corresponding Rows

Mar 21, 2015

select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.

SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor

View 4 Replies View Related

Counting Rows

Feb 15, 2007

is there a way to count the sort of a query and still get the values in the query

columns1, column2, column3 are the same for 5 rows so give the values of each column and then count =5

View 8 Replies View Related

Counting Rows In A Report

Jun 11, 2007

i have a report with 1 group and items under the group.

i want to add a new field called Sl. no. at the group level which keeps the count on groups...by that i mean say if there are 10 groups i want the number from 1 to 10 appear against each group as
1 Grp1
2 Grp2
3 Grp3
.
.
.
10 Grp10

How can i achieve the above. I tried the rownumber but it returns the number of rows the group has. The levels i have is "table1" the main scope and "table1_Group1" the group scope which in the table1 scope.

Any help will be appreciated.

View 5 Replies View Related

Counting Rows While Selecting?

Jan 23, 2008

Is it posible on a query ordered by employee name, for exemple, that MSSQL gives me a position of which employee is contained on the recordset?

that's my query (simplified)

SELECT name, payment, category FROM employee_payments ORDER BY name

John, 1000, sallary
Peter, 1500, sallary
Peter, 500, other
Zeus, 1000, sallary

I want to add another field giving me the position of the employee on the recordset:

John, 1000, sallary, 1
Peter, 1500, sallary, 2
Peter, 500, other, 2
Zeus, 1000, sallary, 3

Is it posible?

View 5 Replies View Related

Counting Rows Per Page In SQL Server 7.0

Aug 11, 1998

Hi all,

Can anyone explain to me how to count number of rows per page? I need to calculate an index size, and I think the factor that contributes the most to my formula`s inaccuracies is the number of rows per page.

Thanks.

Venus Lee.

View 1 Replies View Related

Counting Rows That Refence Another Table

Oct 20, 2005

Hi,

I have two tables,

Table A(A_ID, Info)

Table B(B_ID, A_ID, Blah) where B.A_ID references A.A_ID

How can I detemine how many records in table B reference each unique A_ID in table A?

I've tried the following but it doesn't work:

Select A.A_ID, COUNT(B.A_ID) FROM A
JOIN B ON A.A_ID = B.A_ID

View 3 Replies View Related

Counting Number Of Deleted Rows

Mar 8, 2007

Hi

I have a stored procedure which deletes a number of rows from a number of different tables. How to i count/return the number of deleted rows in each table?

Here is my stored procedure if it helps:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_delete_entry]
@new_venue_id int
AS
BEGIN

DECLARE@new_customer_id int
SET @new_customer_id = (SELECT customer_id FROM VENUE WHERE venue_id = @new_venue_id)

DELETE FROM FEATURED WHERE venue_id = @new_venue_id
DELETE FROM FACILITIES WHERE venue_id = @new_venue_id
DELETE FROM SIC WHERE venue_id = @new_venue_id
DELETE FROM SUBSCRIPTION WHERE venue_id = @new_venue_id
DELETE FROM ADMIN WHERE venue_id = @new_venue_id
DELETE FROM VENUE WHERE venue_id = @new_venue_id
DELETE FROM CUSTOMER WHERE customer_id = @new_customer_id

END

thanks

View 4 Replies View Related

Counting Rows By Date (was Help On Query)

Apr 21, 2004

I hate to ask such silly helps..but I'm missing something here..need help.
I have a table having columns for createddate and deleteddate. The data gets created and deleted periodically and I need to find out the number of created,deleted and remaining number of records on each day. This query works, but takes a lot of time...not sure if there is a more better way to do this.. Please help
SELECT
CAST(createddate AS DATETIME) AS createdDate,
Created,
Deleted,
Remaining
FROM(
SELECT
CONVERT(VARCHAR,createdon,102) AS CreatedDate,
COUNT(1) created,
(SELECT COUNT(1) FROM table ta2
WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
CONVERT(VARCHAR,ta.createdon,102)) Deleted,
((SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
CONVERT(VARCHAR,ta.createdon,102)) -
(SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
CONVERT(VARCHAR,ta.createdon,102))) Remaining
FROM table ta
WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
GROUP BY CONVERT(VARCHAR,createdon,102)
ORDER BY CONVERT(VARCHAR,createdon,102) DESC)
AS tmp

View 13 Replies View Related

SQL Counting Number Of Non-distinct Rows?

May 3, 2006

Hi, I have a table that for ease has this data in:R1, R2, R....z---------------------A | 12A | 22A | 30B | 0B | -1B | -3C | 100I want to generate a table for each distinct row in R1, gives a countof all the rows with data correspondingFor the above table I would getA | 3B | 3C | 1Im probably being stupid but cannot see this at the moment... pleasehelp.Thanks

View 3 Replies View Related

Counting Number Of Specific Rows In SQL

May 22, 2007

hi,

I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.

Thanks,

Dror.

View 7 Replies View Related

Counting # Of Imported Rows For Each Import Process

Nov 25, 1998

hi, I am importing data daily to many tables, I want to keep track of the #of rows for each import process. I already have created a trigger as follow:
CREATE TRIGGER tr_bcp_log ON dbo.A
FOR INSERT
AS

declare @name varchar(30),
@row_count int

select @name=name , @row_count= @@rowcount
from inserted

insert into bcp_tracks (name,row_count)
values(@name,@row_count)
GO

The problem is that I am getting a row for each inserted row in table A.for instance if I have 500 rows in table A, I will get 500 rows in the log table like this
table_name,#of rows
A 1
A 1
A 1
etc up to 500 rows for table A

This is not what I want, I want to capture the num of rows for every bcp process , so in the log table I want to see the following :
table_name, #of rows
A 500
B 600
C 450
A 250
etc

Any help ?

thanks

Ali

View 1 Replies View Related

SQL Server 2014 :: Counting Corresponding Rows In Table

Mar 21, 2015

Select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.

SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor

View 3 Replies View Related

Counting Rows From Flat File Source

Jun 30, 2006

Hello,

Is there a way (perhaps a property) to capture the number of rows selected from a Flat File Data Flow Source without having to develop a script to loop through the rows and count them?

Thanks a lot,

Grace

View 1 Replies View Related

How Trustworthy Is Sys.partitions As A Means For Counting Rows In A Table?

Nov 20, 2007

Hi all,
I have the following function:

Code Block
create function udf_CountRows(@pTableName sysname)
returns int
as
begin
declare @ret int

select @ret = SUM(p.rows)
from sys.partitions p
inner join sys.objects o
on p.object_id = o.object_id
and o.[name] = 'Well'

return @ret
end

Can I trust sys.partitions to always return the correct value or does it suffer the same issue as sysindexes prior to SQL2005?

Thanks
Jamie

View 5 Replies View Related

Counting Occurrence Of A Value && How To Format For Fixed Number Of Rows && Columns

Jan 7, 2008

Hi,

I need to count and display the number of records which have GradeTitle="SHO". I'm only starting to use BI development studio and all attempts at using the built in aggregate functions have failed.

Also, the report I wish to create has a fixed number of columns and a fixed number of rows as the info being displayed is really only counting values in the DB. I tried using Table but multiple rows were created.

I'd appreciate if anyone could point me in the right direction, as searching this forum turned out to be pretty fruitless for me.

Thanks in advance,
John

View 16 Replies View Related

How?: Group By Date And Count Rows In Group

Jan 29, 2007

I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.

this is the best I can come up with:

Code:

SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date

The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.

What am I doing wrong? Thanks!

View 9 Replies View Related

Get One Row From Each Group Of Rows

Jul 22, 2007

Hi,I'm trying to build a query that get only one row from a group ofrows, but I need the values from that row and not the results of onefunction group.I need one row for each idRef, with column2=2 and the bigger column1id |idRef | column1 | column21 1 0 12 1 1 23 1 2 14 2 0 15 2 1 26 2 2 17 2 3 2For these, I will take the rows with id=2 and id=7.Thank you, and sory for my english.

View 2 Replies View Related

TOP X Group Rows

May 31, 2007

Ok, I have searched the forums and the web, and I dont think this is available, but I am going to ask for any ideas...

I need to show a parameterized TOP x report with collapsible detail. For example:

+ CA

+ NY

+ GA

+ CO

+ PA

+ All Others

Again, the user is able to select how many group rows are shown.

Any suggestions or ideas?

Thanks!!

BobP

View 1 Replies View Related

How Do I Use Group By To Get Top Rows?

Sep 21, 2007

Okay, I am sure this is an easy question, but for some reason I cannot wrap my brain around it. I have a table that has data similar to the following:

IPAddress
VisitDate
URLVisited

192.154.21.554
9/18/2007
http://www.microsoft.com

192.154.21.554
9/19/2007
http://www.google.com

164.21.124.23
9/19/2007
http://www.microsoft.com

192.154.21.554
9/20/2007
http://www.yahoo.com

What I am trying to do is use a select query to find the most recent visit per IP address (but I also want the other row data like URL visited). I have something like this that is finding the most recent visit per IP address:

SELECT IPAddress, MAX(VisitDate) AS MaxDateVisited
FROM VisitorTable
GROUP BY IPAddress

How do I also pull in the URL that is associated with the MAX date visited? Thank you!

View 4 Replies View Related

Keep Group Rows Together

Dec 21, 2006

Hi,

Is it possible to keep a group in a table report on the one page if this group could be fitted into the rest of the page and start new page otherwise?

Thanks,
Igor

View 4 Replies View Related

Trying To Get The First Count Of A Group Of Rows

Dec 24, 2003

Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg

Row A = NoteID 1, FK_JobID 1, UnRead

Row B = NoteID 2, FK_JobID 1, UnRead

Row C = NoteID 3, FK_JobID 1, UnRead

I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.

I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.

Heres my attempt

DECLARE @UserID INT

SET @UserID = 4

SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact,
Master_Jobs.Job_Title, Master_Jobs.JobID
FROM User_Notes INNER JOIN
Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN
Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID

WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND
BeenRead = 'UnRead'

Thanks in advance

View 6 Replies View Related

Group Multiple Rows Into One

Jun 20, 2012

creating a query to group identical rows into one and placing corresponding data in appropriate columns for a table named items, what I mean is that I have a table structured as below

Itemcode, Description, Period1, Period2, Period3
001--------Desc--------233,---------,------- <<ROW1
001--------Desc ------------ ,100,------------ <<ROW2
001--------Desc ------,--------,-------,300 <<ROW3

Row one contains a value in Period1
Row two contains a value in Period 2
Row three contains a value in Period 3

I need to create a query so that the end result would look like this;

Itemcode, Description, Period1, Period2, Period3
001 --------Desc -------233 ----100 ---300

creating an sql query to get the above result?

View 2 Replies View Related

Comparing Rows In The Same Group

Feb 26, 2013

Code:

Room ID Hours StartTime EndTime
GR41908101700
GR42409101300
GR43317102000
LR111108100900
LR112109101000
LR113210101200
LR114112101300
LR115313101600
LR116216101800
LR117118101900
LR118119102000
LR119320102300

As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?

View 3 Replies View Related

How To Group Certain Rows And Get The Data.

Jul 2, 2007

Hi Folks,

I am stuck in forming a query.

My age wise employee count sample data (department wise) is as shown below.

Sample Data
-----------
Department [>55] [50-55] [<50] [<40] [<30]
---------- ----- ------- ----- ----- -----
Marketing 0 1 5 10 20
Op's Support 0 3 6 5 25
Op's Tech 0 0 0 3 10
Product Tech 0 0 2 4 12
Product Support 0 0 1 3 7

I would require the data (sum of employee count age wise) to be categorized at a boarder level. Each category comprising of one or more departments.

Operations [Op's Support + Op's Tech], Product [Product Tech + Product Support], Others [Marketing]
The expected result would be.

Category [>55] [50-55] [<50] [<40] [<30] [Total]
--------- ----- ------- ----- ----- ----- -------
Operations 0 3 6 8 35 52
Product 0 0 3 7 19 29
Others 0 1 5 10 20 36

Thanking you in anticipation.

Jabez.

View 4 Replies View Related

UPDATE TOP Row For A Group Of Rows?

Jan 6, 2008

My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.

I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.

SQL Server 2005

Table (many columns left out for simplicity)

ID - unique.
HdrID - key to the header record.
PTtimestamp - date, date/time the row was written to DB.
PType - integer, representing various states of the row.
etc
etc
etc (another 15 columns of data)....

Typically there is approximately 250 records per HdrID.

I am trying to do an SQL UPDATE without using my usual solution of writing vb code!

I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.

ie;

ID HdrID PTimestamp
001 001 01/01/2008 09:00:00
002 001 01/01/2008 09:10:00
003 001 01/01/2008 09:20:00
004 002 01/01/2008 09:00:00
005 002 01/01/2008 09:10:00
006 002 01/01/2008 09:20:00

In the above example I want to update the PType field = -9999 for record ID=003 & ID=006. In reality the table has tens of thousands of rows.

Jake

View 6 Replies View Related

Count Rows In Group

May 1, 2007

Hello.

I built a report with one field as a group.
I want to count the number of rows in each field so I can add it to the group field or somw where in the report.

How can I count how many rows do I have in each group?

Thanks.

View 1 Replies View Related