Most Efficient Way To Count Subset Of Rows In A Table?
Sep 14, 2005
Hi all,
I’ve a table with production objects, and another with possible items composition of the object. I need to count how many occurrences of each standard composition appears:
Table PROD:
PROD_ID COMPONENT TYPE
--------- ----------- -----
1 AAA X1 BBB Y2 AAA X3 BBB Y4 AAA Y5 AAA X5 BBB YTable ITEM_COMPITEM_ID COMPONENT TYPE-------- --------- -----7 AAA X7 BBB Y8 AAA X9 BBB Y
The result should be:
ITEM_ID OCCURRENCES-------- ----7 28 19 1
Table PROD have millions of rows, my way is too slow (I’ve a loop where each PROD object are separately queried against the ITEM_COMP), some have an idea for a most efficient way?
ThX
NeuralC
View 2 Replies
ADVERTISEMENT
Mar 30, 2006
which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query
View 10 Replies
View Related
Jun 25, 2007
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
View 6 Replies
View Related
Jan 29, 2014
I am trying to get counts of contacts.
So how do I subset, to gain a the accounts with the products then query to gain the contact counts from those accounts?
But for some reason it's giving me counts of products.
select distinct
a.region__c as [Region],
a.SFTX_ID as [SFTRX ID],
count(c.Contact_ID) as [Contact Count]
from vwContact c
[Code] .....
View 1 Replies
View Related
Apr 19, 2001
This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.
The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...
Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id
Thanks,
Jim
View 1 Replies
View Related
May 1, 2007
I have been messing around with trying to replicate sql server 2000 tables to postgres as well as mySQL. I'm starting with just one table and trying to get that working, it has around 9000 rows, all of which copy to postgres (with some datatype issues).
However, when I try to replicate to a mySQL database however, only the same 152 rows get copied every time.
I compared rows that were copied to ones that were not to see if there was any obvious differences between the two and couldn't find any.
View 1 Replies
View Related
Jul 15, 2013
I have database test007DB and I need count all table rows then insert into test99 table using ssis packages .
test99: tableName countRows
t1 20
t2 30
t3 25
View 2 Replies
View Related
Jan 17, 2006
I have 2 tables:
TableA:
Name
UserA
UserB
UserC
Table B:
Name Data
UserA xxx
UserB asdasd
UserB ewrsad
UserC dsafasc
UserA sdf
UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count
UserA 2
UserB 3
UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
View 5 Replies
View Related
Apr 10, 2008
I would like to know the best way to count and display the number of rows in a given database table called memberinfo.
This should tell me how many members I have right?
Thanks
View 6 Replies
View Related
Jul 7, 2003
I've noticed that sometimes the # of rows specified in table properties tab is different from the # shown by select count(*) from tableA.
Why is this ?
(SQL 2k, sp2, indexed table)
View 6 Replies
View Related
Aug 26, 2007
I am building a Table Report where I need to "count" the number of cells in a column conditionally.
I have a column of data where the values will be "Orange", "Apple", "Banana", NULL
The pseudocode would be something like this:
iCountOfOranges as Integer
iCountOfApples as Integer
iCountOfBananas as Integer
IF Cell.Value = "Orange" THEN
iCountOfOranges = iCountOfOranges + 1
ELSE IF Cell.Value = "Apple" THEN
iCountOfApples = iCountOfApples + 1
ELSE IF Cell.Value = "Banana" THEN
iCountOfBananas = iCountOfBananas + 1
The 3 count values would then be displayed in 3 footer rows at the bottom of the table.
Thanks
View 4 Replies
View Related
Jan 8, 2015
How can I count the rows when I make new table. Here is my code:
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID, COUNT(TO_CHAR(HIRE_DATE, 'DAY')) AS NUM_EMPLOYEES
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'Day') = 'Monday'
GROUP BY EMPLOYEE_ID, LAST_NAME, SALARY, JOB_ID, COMMISSION_PCT, DEPARTMENT_ID;
Here is the table: [URL] ....
I have 10 rows, so I would like under NUM_EMPLOYEES to be displayed 10 on each row.
View 5 Replies
View Related
Sep 15, 2015
I have a question regarding the total count of the table rows
Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".
So now I have to store the count in 'nn'.
View 5 Replies
View Related
Sep 18, 2015
I've a SSIS 2008 parent/child package solution to manage data transfers between two different data sources, so we can copy multiple tables and capture how many rows were transferred and duration for each transfer. This solution was working fine up until last week, when I made some changes to allow the package to perform a source count using standard SQL determined by an expression, or SQL provided from configuration tables, I also changed the package to Truncate or not the destination table, again controlled by configuration settings in a table. The child packages which perform the data flows have not changed!
The day after the controlling package promotion to live, I saw the bizarre behaviour of the Package log stating all rows transferred, but the actual table counts were not what the log stated, see attached file. The package solution works ok on other servers and was ok in DEV, but there were less tables and rows transferred.Re-running the package gave the same errors, but on some of the same tables and some different ones.
As it is the child packages doing the transfers and nothing has changed in them. I cannot see how the log would be able to say all rows are transferred and yet not all of the rows are actually moved?
Process output - where you can see counts and log Table transfer controller (as txt not dtsx)
An example of the data transfer child packages (as txt not dtsx)When I set the ExecuteOutOfProcess = True the package worked fine, unfortunately, this is not a good solution as SSIS 2008 does not tidy up the Dtshost.exe processes it starts and I'd be left with a memory issue after a very short time, we transfer hundreds of tables each day. ( I could write a .net script in the controlling package to kill the child processes, but that would still have hundreds of processes running before I could end them, as we have three parallel streams to allow a bit better performance.
View 6 Replies
View Related
May 1, 2007
hi i have a table as follows:
col1 col2 col3
2 BB 2
1 AB 3
3 CA 3
1 AC 1
2 BA 4
3 CB 2
2 BC 6
1 AA 2
3 CC 5
i want to output the whole row of each unique col1 where its col3 is max, so that i will get the ff result set
1 AB 3
2 BC 6
3 CC 5
i have an idea which is to use a cursor but i don't know the best/fastest way of doing it.
thanks heaps in advance!
View 1 Replies
View Related
Mar 19, 2015
IF OBJECT_ID('tTable') IS NOT NULL
DROP TABLE tTable
GO
CREATE TABLE tTable
(nRow_IdINTEGER IDENTITY NOT NULL PRIMARY KEY,
nParent_IdINTEGERNULL,
[Code] ....
gives:
nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C
I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows
4 4 Two A
5 4 Two B
6 5 Tow C
View 5 Replies
View Related
Oct 25, 2007
I'm beating my head up against a wall here. I have one very LARGE table called op_hist.
CREATE TABLE [dbo].[op_hist] (
[op_trnbr] [int] NOT NULL ,
[op_type] [varchar] (80) ,
[op_date] [smalldatetime] NULL ,
[op_act_setup] [decimal](38, 10) NULL ,
[op_act_run] [decimal](38, 10) NULL ,
[op_qty_comp] [decimal](38, 10) NULL ,
[op_wkctr] [varchar] (80) ,
[op_part] [varchar] (30)
)
I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.
SELECT op_wkctr, pcs, hrs.b
FROM
dbo.op_hist
INNER JOIN
(
SELECT op_part, SUM(op_qty_comp) as pcs
FROM dbo.op_hist
where op_type='BACKFLSH'
group by op_hist.op_part
UNION ALL
SELECT op_part, SUM(op_act_setup + op_act_run) as b
FROM dbo.op_hist
where op_type='LABOR'
group by op_hist.op_part
) AS hrs
ON op_hist.op_part = hrs.op_part
where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'
Help me oby one - you're my only hope.
Sincerely,
Frustrated!
View 4 Replies
View Related
Jul 20, 2005
Hi,I was wondering if you guys have any nth script to reads from tableand outputs into a temp table subset of records. There was a nth toolI used to use it was GROUP1 which was written in C and it used to bevery fast on nth -in a flat file. In this program we used to pass fewparamaeters. For example if I want 30,000 records from the file of500,000. The function seams to work something like this. you dividethe 30,000 records of 500,000 which will result with .090909090909.Now we would pass only the first 7 digit (0909090) as parameter thatwould nth the file down to 30,000 records. This function allwaysworked whichever number you use as long as the read file is largerthan output fileI like to use the similar concept in Sql Server and I was wondering ifanyone has any script to do this or how to go about this?Thank you. I appreciate your feedbackagron
View 1 Replies
View Related
Mar 21, 2007
DB design question
I have multiple tables with information about a user. The tables are Roles, Users, Groups and Profiles.
For a user session I need information from all those tables. Would it be better to make a table called UserSession and collect the necessary data from the above mentioned tables and stick them in one the UserSession table or should I just write a query that goes out and gets the data from the different tables.
What is better practice and what is faster?
View 4 Replies
View Related
Aug 24, 2006
Hi All,
I have a table A. I need output subset of a table A to a flat file using query, like:
select A.* from A inner join B on... ..... inner join C......where left(A.id, 3) = B.sid.... AND B.num between 100 and 200).
How can I do this in SSIS? Which data flow item I may need?
Thanks
View 2 Replies
View Related
May 13, 2015
I created a CTE which finds a subset of records from a table
I then ran a SELECT statement against the same table as
SELECT * FROM TABLE
EXCEPT (SELECT * FROM CTE)
Is it possible to add another EXCEPT statement after the CTE EXCEPT statement to cover a condition not incorporated in the CTE definition?
View 9 Replies
View Related
Feb 8, 2007
I am running a website of crossword puzzle and Sudoku games. The website is designed to be:
There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.
The current solution is:
3 tables are designed for the functions mentioned above.
Table A: UserTable --storing usering information, userid
Table B: GameList --storing all the game information.
Related fields:
GameID primary key
FinshiedTimes recording how many times the game has been finished
Table C: FinishHistory --storing who and when finished the game
Related fields:
GameID ID of the game
UserID ID of the user
FinishedDate the time when the game was finshied
PS: Fields listed above are only related ones, not the complete structure.
Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.
When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.
When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.
Existing Problems:
With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.
Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?
View 5 Replies
View Related
Dec 19, 2007
There are 2 tables
tPeople
ID Name
1 Arun
2 B
3 C
tAddress
PeopleID Address
1 Test Address A
1 Test Address B
1 Test Address C
2 Address A
3 Address1
3 Address2
Expected Result
ID Name Address1 Address2 Address3
1 Arun TestAdressA Test Address B Test Address C
2 B Address A
3 C Address1 Address2
Address columns repeated = max(count(Address)) for PeopleID
Help me on this!
I can do it through dynamic sql using Execute sp_executesql
Is there any workaround except this?
View 2 Replies
View Related
Apr 5, 2001
I have a table called Cartype and there is field called typeid contained 5 rows such as A,B,D,C,E,F
Ihave another table called transaction that the field called typeid and the data contains 3 rows of A and 3 rows of B , and 1 rows of E
I want to write the sql that output something like this
A 3
B 3
D 0
E 1
F 0
thanks
View 1 Replies
View Related
Mar 13, 2008
Is it possible to count total no. of rows in a table without scanning whole table??
View 10 Replies
View Related
Sep 25, 2007
Hi,
I am using a foreach file to loop through mdb files kept in a folder and then transfer them to Sql server.
I want to do the following -
Sum the total no. of rows in mdb while the package executes
Sum the total no of rows transferred to Sql server.
Write them to a database (InitialRowCnt, FinalRowCnt, PackageNm, UserNm)
Note: Total = Total rows in all mdb files. so if there are 10 mdb files with 10 rows each, total = 100.
I saw a RowCount transformation..but just dont know where to place and what to do with it.
thanks
View 4 Replies
View Related
Jan 4, 2007
I have a view that I want to find all the rows that have a matching itemid and have more than 3 rows in them and group them by the itemid.
I am not quite sure how to do this.
Any ideas?
~mike~
View 5 Replies
View Related
Dec 20, 2007
SELECT ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHERE EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita
hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)
View 8 Replies
View Related
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
Mar 1, 2001
Hello,
Do anyone knows how can i count rows in all the tables in a database.
With select count(*) I can count rows only from one table .
TIA.
View 2 Replies
View Related
Dec 29, 2006
I'm new to MS SQL having used MySQL for several years now. I just can't figure out the syntax on counting or selecting rows
a) in the Last X (days, weeks, months...)
or
b) between "date X" and "date Y"
I've tried this to get the last week:
SELECT COUNT(*)
FROM contacts
WHERE CONVERT(datetime,timestamp) < (GETDATE() - DATEADD(DAY,7,GETDATE()))
...but I always get a total that includes rows outside the span.
Any suggestions? Thanks!
View 7 Replies
View Related
Nov 11, 2005
hey, how would i count the number of rows, with out using a loop??
thanks, Justin
View 2 Replies
View Related
Oct 23, 2013
The following query returns 2142 rows which is correct.
Code:
select COUNT(*), sum(v.currentMarket)
from TRMaster m
inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
group by m.Year, m.Parcel
having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
View 4 Replies
View Related
