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


ADVERTISEMENT

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 View Related

Counting And Total

Jun 10, 2006

Hi All

I have:

select prod_id , count(prod_id)[Number of Times]
from orders_tbl
group by prod_id
order by 1;

I get a result however, how do sum up the total number of times ?

Thanks

View 5 Replies View Related

Query By Year Group By Total Students Sort By Total For Each County

Jul 20, 2005

I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob

View 4 Replies View Related

Reporting Services :: SSRS Group Total Expression - Add Total Disabled

Oct 26, 2015

For some reason my Add Total is grey out, when i tried to add grand total using some expression.

I have two row & two column groups?

Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image

I'm using iff condition in my expression.. 

View 15 Replies View Related

Counting Parent Records And Displaying Total??

Feb 5, 2004

We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:

parameter # Calls
------------ --------
desktoppp 5
desktopqq 6 {This is what I am getting at this stage}
desktop t 4
network
r 9
networkgg 10
softwarevv 3

This is what I would like to have:

parameter # Calls
------------ -------
desktop 15
network 19
software 3

Please Help!

View 2 Replies View Related

Counting Total Number Of Queries Executed Within The Page

Aug 25, 2007

Hi everyone,Does exist an easy way to count the actually number of queries executed within a page?I've searched here and in google but found anything...Thanks in advance! 

View 3 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

What's Microsoft Doing About Providing Page Numbering Per Group And Total Pages Per Group?

Nov 27, 2007

Hi!

I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.


https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679

Provide Individual Page Numbering per Group and Total Pages per Group

Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.


I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.


Thanks!
Karen

View 1 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

Group By Sum And Total

Oct 22, 2007

we have following table sturcture

CREATE TABLE [PPE_STOCK] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PPE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PPE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_RECEIVED] [datetime] NULL ,
[QUANTITY_PPE] [int] NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO



CREATE TABLE [PPE_ITEM_ISSUE] (
[PII_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QUANTITY_ISSUE] [int] NULL ,
[REMARKS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO





CREATE TABLE [EMPLOYEE] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FIRST_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAST_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


data insert like this

insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH01','SAFETY HELMET',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH02','SAFETY GLOVES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH03','SAFETY SHOES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY_PPE) values
('SH04','SAFETY GLASSES',100)
.....



insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006367','sh01',2)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006365','sh01',1)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006366','sh02',5)
insert into ppe_item_issue (EMP_ID,PPE_ID,QUANTITY_ISSUE) values
('p0006367','sh01',5)
.....



insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006367','Martin','1')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006365','Peter','2')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006366','Jones','3')
insert into EMPLOYEE(EMP_ID,NAME,POSITION_code) values
('p006368','Peter','4')
.....


How can retrived the data by the query like this group by sum
and total.

PPE_STOCK : SAFETY HELMET total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....1...
p0006365..Peter......2
p0006366..Jones......1
-------------------------------------------------------
..............................4......4%

PPE_STOCK : SAFETY GLOVES total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....5...
p0006365..Peter......2
p0006366..Jones......5
-------------------------------------------------------
..............................12......12%


PPE_STOCK : SAFETY SHOES total QUANTITITY_PPE 100
-------------------------------------------------------
EMP_ID, NAME, QUANTITY_ISSUE, SUM......%
-------------------------------------------------------
P0006367..Martin.....4...
p0006365..Peter......4
p0006366..Jones......6
-------------------------------------------------------
..............................14......14%
...
...


Regards
Martin

View 5 Replies View Related

Sum Per Group Rather Than Total

May 21, 2008

Can somebody please help me how to show a total per 'group' in a table, rather than the total sum?

I have a report with a list that's grouped per project. This list has a table showing amounts paid to this project. As a footer to the table, I'd like a sum that shows the total amount paid per project.

Unfortunately if I run the report on more than one project, the list and table will still be grouped correctly, but the 'sum' will be the total sum rather than the sum of just that given project.

Eg:

Project 1
1-7-2007, 20,000
2-7-2007, 40,000
Total, 100,000

Project 2
2-7-2007, 40,000
Total, 100,000

I've tried all I can think of to get the 'group sum' rather than the total sum, and am sure I'm missing something really basic. I hope somebody here can help me.

Thanks in advance,
Maria

View 6 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

Group Total Summary Help

Apr 12, 2007

I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example










Group A


PK Field
Summary Data Field

1
250

2
300

Group A Total
550

Group B


3
100

4
50

Group B Total
150

Grand Total
700



The underlying query contains detail data and I am using a table with two group levels. All details are hidden.



To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.



Thanks,

Simone

View 9 Replies View Related

Total Of A Group In Table

Sep 20, 2007



I have a report that looks like below. It's grouped by Product then by Year. I want to include within the Product grouping and item called "All products". If I swapped order of Grouping to Year then Product, I could simply add a SUM in the group header, but the users don't want it displayed that way round.
Any suggestions for "All products" ?
Thanks
Richard

Revenue
Product 1
2007
2008
2009
Product 2
2007
2008
2009
etc

I want to add
All products
2007
2008
2009

View 2 Replies View Related

Creating A Sub Total Using Group By

Apr 5, 2008

I have a table that I want to summarize the amount column and have it show the sub totals for the employee for each pay period. For example:

EMPLOYEE PerEndDate EarnCode Amt
A 8/1/2007 Hourly 100
A 8/1/2007 Peices 250
B 8/1/2007 Hourly 75
B 8/1/2007 Pieces 300
A 9/1/2007 Hourly 50
A 9/1/2007 Pieces 200
B 9/1/2007 Hourly 100
B 9/1/2007 Pieces 200

What I want to show is

Employee PerEndDate Amt
A 8/1/2007 350
A 9/1/2007 250
B 8/1/2007 375
B 9/1/2007 300

I'm using:


SELECT PayPeriodNo, EMPLOYEE AS PayEmpNo, MIN(PerEndDate) AS PerEndDate, SUM(AMT) AS Amt
FROM dbo._vPayroll
GROUP BY PayPeriodNo, EMPLOYEE
ORDER BY PayPeriodNo, PayEmpNo

And the quantity is showind grand totals for each instance

Employee PerEndDate Amt
A 8/1/2007 600
A 9/1/2007 600
B 8/1/2007 675
B 9/1/2007 675

Can anyone help me with this?
Thanks
Leo

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved