Hi, I have a table that for ease has this data in:
R1, R2, R....z
---------------------
A | 12
A | 22
A | 30
B | 0
B | -1
B | -3
C | 100
I want to generate a table for each distinct row in R1, gives a count
of all the rows with data corresponding
For the above table I would get
A | 3
B | 3
C | 1
Im probably being stupid but cannot see this at the moment... please
help.
Thanks
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
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.
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
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
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
how can I update Table 1 to get desired results?
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
How can I update Table 1 to see desired results?
I am trying to write a SQL DataSource Statement that will do the following:
Select the Distinct Dates, count up the number of rows with that date
So for example:
Date Number with that Date
12/12/2007 3
14/12/2007 2
Database:
12/12/2007 Content 1
14/12/2007 Content 2
12/12/2007 Content 3
14/12/2007 Content 4
12/12/2007 Content 5
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
Counting groups
I have the following code that is grouped by name
Mike G ( from 2000 2003)
Address Raise
Test 1
Test 1
Mike G ( from 2004 2007)
Address Raise
Test 2 1
Test 2 1
Mark G ( from 2004 2007)
Address Raise
Test 2 1
Test 2 1
Total 6 (I get this without any problem)
The problem I am having is trying to find out how many groups we have to use the value for some other calculation. So I did this Count(Field .Name) wich will give me 6 instead of 3 . The reason is in my dataset it will be something like that
Name add raise Period
Mike G Test 1 2000-2003
Mike G Test 1 2000-2003
Mike G Test 1 2004-2004
Mike G Test 1 2004-2007
Mike G Test2 1 2004-2007
Mike G Test2 1 2004-2007
If I do CountDistinct(Field .Name) it will give me 2 which is also wrong . So how can I count many groups we have. I spend a lot of time on this please help!
Thanks
I'm trying to figure out how to do the following:
Number of People receiving their second speeding ticket during this time frame
4 Jun 06 -3 Jun 07
4 Jun 07 -3 Jun 08
4 Jun 08 -3 Jun 09
4 Jun 09 -3 Jun 10
4 Jun 10 -3 Jun 11
4 Jun 11 -3 Jun 12
The table would contain historical data and look something like this
CREATE TABLE [dbo].[test](
[person_id] [NCHAR](10) NULL,
[ticket_date] [DATE] NULL,
[ticket] [BIT] NULL
) ON [PRIMARY]
GO
COUNTING NUMBER OF SELECTS MADEtable mytable {id, data, hits}users view data from the table:SELECT data FROM mytable WHERE id=1 --for exampleSELECT data FROM mytable WHERE id=20 --for example....How do increment the hits column without replacing the above with the below?update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=1update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=20....I believe triggers can't be used as they only trigger on update/delete events.I'm using sql server 2000 (latest patches) with aspThanksAlex
View 3 Replies View RelatedI am having a problem transforming a data set to the structure I need. I have data in the following format.
Id Visit1 Visit2 Visit3 Visit4
1 2Mar2010 27Mar2010 24Apr2010 8Jul2010
2 2Apr2010 3May2010 4Jun2010 11Jul2010
I need to transform it into a table which gives a count of the number of subjects at each visit at the end of each month.
For example, in March there will be 1 subject at visit 2. In April, there will be a total of 2 subjects, 1 at visit 1 and 1 at visit 3. In May there will be 2 subjects, 1 at visit 3 and 1 at visit 2, etc.
Here is the table I was hoping to produce.
Date.....Subjects.......Visit1.....Visit2.....Visit3.....Visit4
March....1.............................1
April......2...................1.........................1
May.......2.............................1..............1
Jun.......2............................................2
Jul.......2...........................................................2
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 Relatedhi 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
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?
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 ?
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
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
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 RelatedHi,
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
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.
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?
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
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.
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
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
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
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
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
In report builder 2.0, I cannot determine how to display the number of times a field has been filled out when a Β one to many relationship is involved. I get an error every time I use two aggregates.
ID, Symptom_ID
1, 111
2, NULL
3, 222
3, 333
3, 444
4, NULL
5, 666
The result should be 3 out of 5 records... Or even better displayed as a percentage of the total 60%
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View 4 Replies View Related
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