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


ADVERTISEMENT

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

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

SQL Server 2014 :: Replace Rows With Other In Same Table

Jul 5, 2015

I have the below table :

IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0

Then i need to replace second row with value with first row with null and so on :

IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL

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

SQL Server 2014 :: Insert 500 Million Rows Into In-memory Table

Jul 29, 2014

I am doing a performance testing for In-memory option is sql server 2014. As a part I want to insert 500 million rows of records into a in-memory enabled test table I have created.

I need a sample script to insert 500 million records into a table ....

View 9 Replies View Related

SQL Server 2014 :: Find Equal And Opposite Rows In A Table

May 25, 2015

How to find the equal and opposite rows in a table.

E.g.:

book position
A 500
B -500
C -500

The output should return the rows having equal and opposite positions.

O/P:
A 500
B -500

I have tried self join on a table but in vain.Do we need to cursors for these row level handling?

View 6 Replies View Related

SQL Server 2014 :: Huge Number Of Rows In Table Spool

Jul 6, 2015

I have a CTE query against a table with 32K rows that runs fine in 2008R2. I am running it in 2014 Std Ed. against the same data and it runs very slowly. Looking at the execution plan I think I see what's contributing to the slowness.

Note that the "actual number of rows" is some 351M...how is this possible?

the query:

declare @amts table (claim int,allowed decimal(12,2),copay decimal(12,2),deductible decimal(12,2),coins decimal(12,2));
;with unpaid (claimID) as (select claimID from claim where amt+copay + disct+mm + ded=0)
insert @amts
select lineID, sum(rc), sum(copay), sum(deduct),
case when sum(mm)>0 and (sum(mm)<sum(mmamt)) then sum(mm) else 0 end
from claimln
where status is null
and lineID not in (select claimID from unpaid)
group by lineID

it's like there's some massively recursive process going on?

View 5 Replies View Related

SQL Server 2014 :: Pivot Table With Column Names To Rows?

Aug 1, 2015

I have a table with following rows.

FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000

I am looking to convert it to

Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000

I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun

SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable

Results:

MONTHBNQ FNB RS
Jul11054839 89032 1067299

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

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 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 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 The Inserts And Updates On A Table In A Sql Server Database

Jul 20, 2005

Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish

View 3 Replies View Related

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

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

SQL Server 2014 :: DateDiff Between Two Consecutive Rows

Oct 15, 2014

How can i calculate datediff (in minutes) between two consecutive rows without using CTE & Temp tables?

I was using this successfully, but i need something without CTE and temp tables (both of them are not supported in the tool i am trying to generate a report using custom query).

WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS RowNo, * FROM MyTable)
SELECT t1.*, ISNULL(DATEDIFF(mi, t2.CreateDate, t1.CreateDate), 0) AS Duration
FROM CTE t1
LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo - 1
ORDER BY t1.Id desc

View 5 Replies View Related

SQL Server 2014 :: Comparing Two Rows Values?

Oct 30, 2015

I need to compare two consecutive rows (if BEGDA of second row is 1 day greater than ENDDA of first row then I need to pick First row BEGDA and 2nd row ENDDA)

Input Table Data:

PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2008
10104210001/1/200912/31/2009
10104220001/1/20081/15/2008
10104220001/16/200812/31/2008
10104220001/1/200912/31/2009
10104230001/1/200812/31/2008
10104230001/1/200912/31/2009
10104230001/5/201012/31/9999
101042DDPP5/16/200712/31/2007
101042DDPP2/16/20075/15/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007

Output should be like this:

PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2009
10104220001/1/200812/31/2009
10104230001/1/200812/31/2009
10104230001/5/201012/31/9999
101042DDPP2/16/200712/31/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007

View 3 Replies View Related

SQL Server 2014 :: How To Append Rows Horizontally

Nov 4, 2015

I'm trying to append rows horizontally - I'm using the "xml path" approach

SELECT
E.[USER_NAME] As 'User Name',
(
SELECT ',' + C.[PERMISSION_NAME]
FOR XML PATH('')
) As [Associated Groups]
FROM TABLEA As A
JOIN TABLEB AS B ON A.PK_OBJ_ID = B.FK_APP_OBJECT_REF
JOIN TABLEC AS C ON C.PK_PERMISSION_ID = B.FK_PERMISSION_REF
JOIN TABLED AS D ON D.FK_PERMISSION_REF = C.PK_PERMISSION_ID
JOIN TABLEE AS E ON E.PK_PERSONNEL_ID = D.FK_PERSONNEL_REF
WHERE A.[OBJECT_NAME] = 'MyObjectName'

It's not working. I'm getting:

User nameAssociated Groups
A. SmithG1
A. SmithG2
A. SmithG3
etc...

What I'm looking for is:

User NameAssociated Groups
A. SmithG1, G2, G3
etc...

View 3 Replies View Related

SQL Server 2014 :: How To Copy Data Rows From Hosting To Local Server

Mar 11, 2015

I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:

We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.

Here is what I want to do:

I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.

View 2 Replies View Related

SQL Server 2014 :: BCP Reports No Errors But Inserting No Rows

Feb 15, 2015

I have a Job that creates .CSV files at predetermined intervals (the delimiter is a tabstop) and every 5 minutes I have to retrieve These files one at a time and Import them into the relevant database.

My solution was to create a list of the files and insert this list into a temporary table where a Cursor would extract each filename one at a time and insert that Name into a BCP Statement. The BCP Statement is constructed using dynamic SQL.

Here the Cursor:

create table #filelist(filename nvarchar(100))
insert into #filelist exec xp_cmdshell 'dir C:BCP_Test /A-D /B'
declare @filename nvarchar(100)
declare @path nvarchar(100)
declare @bcp_open nvarchar(100)

[Code] ....

The code seems to work but although there are around 50000 lines in the .csv file, None of the lines are inserted.

View 6 Replies View Related

SQL Server 2014 :: Check If Two Rows Have A Different Value In A Specific Column

Sep 9, 2015

I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.

Like this:

ID IS NULL
1 1
2 1
3 0
1 0

The expected ID i get as a result of my query should be 1 in this case.

View 6 Replies View Related







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