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
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.
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 ....
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?
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
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.
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
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
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.
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
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
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
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 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
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.
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
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?
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
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)
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...
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.
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.
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.
