Count Of Specific Records
Jan 30, 2004I'm having problems constructing a query. I need to get a count of emails in my database, but only the emails that appear 2 or more times.
Can anyone help?
Thanks!
Justine
ADVERTISEMENT
Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records
Nov 19, 2015If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
[Code] ....
Count The Specific Value In The Whole Table
May 8, 2008I am trying to count the number of value "99" for each column in the whole table
to see how many "99" are there per column for the whole table...
and get the percentage per each column.. How can I do this?
output should look like
Column A 117 10%
Column B 120 14%
-- etc..
Get A Count On A Field For A Specific Value
Sep 13, 2013I am attempting to combine to queries that I created and am not having success. I need to get a count on a field for a specific value and have to do this by gender and by an age range. So here is the query I came up with. I don't get any errors produced but I also don't get any results.
$query = "SELECT p1.sex, age_group, COUNT(CASE WHEN p4.q1 = 'Yes' THEN p4.q1 END) AS heart_attack FROM (select p1.sex,".
" CASE WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 11 AND 20 THEN '11-20'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate()))
[code]....
Count SPECIFIC Characters In A String
Dec 30, 2003How do I count the number of specific characters in a string ?
Example:
declare @var as varchar(50)
set @var="1abc1efg1"
If I wanted to count the "1"...I'll get "3" for answer.
This could maybe done by using a while loop, but is there any T-SQL command for this?????
Writing Expressions To Count A Specific Value
Jan 29, 2008
Hello,
Im trying to write an expression in a table cell which counts the number off records in a dataset given it is a certain value..
first column cell =count(Fields!idproduct.Value) = 'broadband' row heading = New Sales
second column cell =count(Fields!idproduct.Value) = 'adsl'
I can create a matrix report which will achieve this however I need to create a few other calculated columns which a matrix table doesnt allow hence im trying to do this via expressions in a normal table, can this be done with IIF statements?
Can someone help
thanks
Count Only Specific Comma Separated Values
Jan 5, 2015I'd like to limit my query results to only items that match any part of a dynamic csv string table but am having some trouble (postgres SQL). Details: I need to calculate how many hours our staff spends seeing clients. Each staff has different appointments that can count toward this. The specified appointments for each staff are listed as comma separated values. My existing query calculates the appointment hours for each staff in a given time period.
However, I need limiting my query to only include specified activities for each staff. My current where clause uses IN to compare the appointment (i.e. activity) listed in the staff's schedule with what is listed an an approved appointment type (i.e. performance target activity). The query runs but it seems to only count one of the activities listed in the csv rather then count all the activities that match with the csv.
select (sum (kept)/60) from (select distinct rpt_scheduled_activities.staff_id as sid,
rpt_scheduled_activities.service_date, rpt_scheduled_activities.client_id,
from rpt_scheduled_activities inner join rpt_staff_performance_target on rpt_scheduled_activities.staff_id = rpt_staff_performance_target.staff_id where
[code]...
Power Pivot :: How To Count Rows Where A Specific Value Exist
May 20, 2015This is primarily a question for DAX/Power BI. I'm trying to figure out how to count rows for a particular column where only a specific value exist. For instance, I'm trying to count how many times a certain field contains a true value or false value across multiple columns.
I found this formula here: [URL] ....
=CALCULATE(COUNTROWS('table'), ALLEXCEPT('table', 'table'[n_country]))
However, I'm not quite sure how to adapt it to count only rows where a value is equal to True, False, 17, "foobar", etc...
Transact SQL :: How To Count Number Of Specific Columns Of Table
Jul 11, 2015I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.
i.e. EmployeeID: 001
week1: 40
week2: 24
week3: 24
week4: 39
This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?
Case Statement - Count Orders That Fall Into Specific Dollar Buckets
Aug 2, 2013I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!
For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:
Code:
CASE
WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
...
WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]
Is there an easier way to do this, maybe with a loop?
How To Restore Specific Records
Jul 23, 2004Scenerio:
Its 3pm and a user comes to me and says, she's deleted an invoice with many associated items. I know the affected tables (foreign keys) and I have last nights backup of the db. However I don't want restore the entire db back from last night just the deleted invoice record/s. What is the best practice procedure for accomplishing this?
Getting All Records By Specific Year
Jan 24, 2007I have a column session_date that holds the date in the format: m/d/yyyy or m/dd/yyyy
how do I write my select statement to grab all records by a specific year?
SELECT *
FROM mytable
WHERE session_date =
Thanks for any help.
Run Report With Specific Records.
Dec 26, 2006I am not sure where i should post this question since it falls both in Report Server and T-Sql but here goes...
I currently need to run a Report that has only specified records that the client/user wants by clicking the check in the check box next to the record they want. They can pick as many or a few of the records that want then run a report only with the records they indicated they wanted... i am thinking they will need some kind of t-sql statement either a function or temp table but i am not sure if even that...
if anyone has any ideas please reply...
Thanks,
WoFe
EXAMPLE: Instead of running a report on records 1, 2, 3, 4, 5, 6, 7, 8, 9
they would run the report on records: 2, 5, 6, 9
Getting Specific Records: Architecture Question
Sep 6, 2007This is out of my league. I’m hoping to get some good advice from someone experienced in the area. My inquiry is how to best handle large amounts of records, say 500,000 records or so. I am web programming and can’t send all this info from server to client.
Part of the problem is the manner in which the data gets stored. I cannot calculate what records I need to get for a distant page (i.e. if 10 items per page then where is my data getting page #512).
These are the very first five (5) records. First row is the primary key.
14
451
0
V5
2 vials
1
V5
8/10/2007
3
20
451
0
V10
2 vials
2
V5
8/10/2007
3
25
451
0
V5
2 vials
1
V5
8/15/2007
3
26
451
0
V10
2 vials
2
V5
8/15/2007
3
27
451
0
V40
2 vials
8
V5
8/15/2007
3Because records 1 through thirteen had been deleted, the primary keys for the first (5) are no longer 1, 2, 3, 4, 5. Had this been the case, a person could easily retrieve page 512, by mathematical calculation.
Page 1 would have been Records 1..10
# 2 = > 11..20
# 512=> 5111..5120.
I already have a program that loads the entirety into an arraylist; then picks out the page of data from the arraylist location. I could rewrite things so that a temporary SQL Table is created – but I don’t know is that a good idea?
All advice welcome - TIA
Retrieve Specific Number Of Records
Mar 5, 2008Hi,
How to display specific number of records? That means I want to display records starting from 3th row to 5th row. Please send your suggestions or links.
Table
-----
Name Age
-----------------
Raja 23
Kumar 26
Suresh 30
Rani 22
Subha 32
Ganesh 25
The result will be
Name Age
-----------------
Suresh 30
Rani 22
Subha 32
Thanx.
GANESAN MURUGESAN
Select Specific Number Of Records
Jul 20, 2005Dear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."
View 3 Replies View RelatedUsing Min Or Group By To Return Specific Records
Mar 12, 2008
I am fairly new to transact SQL and I am having difficulty retrieving the set of records I require given the data shown below. I want to be able to filter the records just to return the records that have the minimum securityorder for each unique secsyscode. I suspect I need to use min or group by to achieve the desired affect but cannot seem to get it right
any help would be appreciated
eg in the following
secsyscode, securitytypecode and securityorder are integers and securityCode is a char(16).
secsyscode
securityCode
securitytypecode
securityorder
1
Special
1
2
2
Total Fund
999
17
3
PerfInd
995
14
3
PerformanceIndex
999
17
4556
93152
1
2
4556
10815-0
4
1
4557
558372
1
2
4557
12137-0
4
1
4558
656113
1
2
4558
13154-0
4
1
4559
53673
1
2
4559
13672-0
4
1
I only want the following records to be returned.
secsyscode
securityCode
securitytypecode
securityorder
1
Special
1
2
2
Total Fund
999
17
3
PerfInd
995
14
4556
10815-0
4
1
4557
12137-0
4
1
4558
13154-0
4
1
4559
13672-0
4
1
Updating A Specific Amount Of Records In A Table
Jun 20, 2007I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
Thanks,
HOW TO SEND SPECIFIC RECORDS IN DATA FLOW
Apr 21, 2008
hello, i have 100,000 records in my table. Out of that what means to me is only 500 records that I want to pass thru and send it to destination.
The data I am trying to transport has a column ID with Not Null values in it.
Please let me know how can we do it,
Can we use a look up for ID from the other tables to just pass thru when it finds an ID match,
Am i correct.
Write A Query To Group Records Based On Speed (specific Value Of Zero)?
Jun 28, 2012I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:
Table - Vehicle_Event
Vehicle_Id____Date_Time______________Speed
C1____________2012-06-28_10:10:00____5
C1____________2012-06-28_10:11:00____0
C1____________2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00____4
C1____________2012-06-28_10:14:00____3
[code].....
OUTPUT_Required:
Vehicle_Id____Date_Time___________________________ __________Speed
C1____________2012-06-28_10:10:00___________________________5
C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00___________________________4
C1____________2012-06-28_10:14:00___________________________3
C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0
[Code] .....
I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.
SQL Server 2012 :: Evaluating Rows And Selecting Specific Records
Mar 18, 2015I have a table with some examples below. I need to identify records where:
1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.
PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely
[code]....
Problem: Mssql Locks Several Records In A Specific Table, While No One Is Editing Them
Jul 20, 2005Hi,I have an Access application with linked tables via ODBC to MSSQLserver 2000.Having a weird problem, probably something i've done while not beingaware of (kinda newbie).the last 20 records (and growing)of a specific table are locked - cantchange them - ("another user is editing these records ... ").I know for a fact that no one is editing records and yet no user canedit these last records in the MDB - including the administrator -while able to add new records.Administrator able to edit records in the ADP (mssql server) where thetables are stored.Please help, the application is renedred inert .Thanks for reading,Oren.
View 3 Replies View RelatedTransact SQL :: Comparing 2 Records / Multiple Instances In The Same Table For A Specific Combination?
Jun 10, 2015I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
Get Records Count
Nov 18, 2007i have this function
it return 0 but the sql statement in the sql query return the right number?how is that
i want to get the number of records any other idea or fix?
Public Function UserAlbumPhotoQuota(ByVal userID As Integer) As BooleanDim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
Dim strSQL As StringDim dr As SqlDataReader
strSQL = "SELECT *, (select count(*) from userAlbumPic where userID=" & userID & ") as rec_count from userAlbumPic "Dim cmd As New SqlCommand()cmd = New SqlCommand(strSQL, Conn)
Conn.Open()
dr = cmd.ExecuteReader()
dr.Read()userQuota = dr("rec_count").ToString
Conn.Close()
End Function
Help With Count Records
Jan 14, 2008Hello All,
I'm wondering if you guys can help me with a problem to count every record in a table; however, I must match this table with another table to get the category names. I have tried this SQL statement in the SQL Express and it works very great.
SELECT aspnet_Category.CategoryName, COUNT(*) AS Expr1FROM aspnet_Category INNER JOINaspnet_resources ON aspnet_Category.ApplicationID = aspnet_resources.ApplicationId AND aspnet_Category.CategoryID = aspnet_resources.CategoryIDGROUP BY aspnet_Category.CategoryNameORDER BY aspnet_Category.CategoryName
However, when I tried to put this into my code, it gives me a error. <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, True)> _Function GetDataByCount_CategoryID() As CategoryDataTableGetDataByCount_CategoryID = Adapter.GetDataByCount_CategoryIDEnd FunctionFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Can you please help me to fix this error?
Thank you, Vic.
Count The Records
Mar 5, 2004I need to export records to a text file and simply index them. i.e. in the pipe delimited file I need a column with the ordinal value of each row.
Example:
1|"Jane Doe"|"23 Western ave"|...
2|"Jamie Delom"|"5 East Street|...
3|"Nat Girshon"|"5678 Main Street|...
.
.
Would anyone be able to tell me how I could build this within the SQL query without creating a physical table and using IDENTITY functions?
Count All &>0 Records
Jan 24, 2007count(foundedamount>0)
does not seem work, how can I count all record and fundedamount>0?
THX
Count Records
Jan 20, 2008Hi
i am just an beginner with sql and i am quite sure this will be a simple question
i am looking for a methode to count the number of records how can i do this?
greetz
SQL Server 2012 :: Using Merge Statement Output Records To Rebuild Table On Specific Date
Mar 5, 2015I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
How To Count The Records In A Datatable
Mar 4, 2007I have the following: Dim ResourceAdapter As New ResourcesTableAdapters.ResourcesTableAdapter Dim dF As Data.DataTable = ResourceAdapter.GetDataByDistinct Dim numRows = dF.Rows.Count Dim dS As Data.DataTable = ResourceAdapter.CountQuery Dim sumRows = dS.Rows.Count DataList1.DataSource = dF DataList1.DataBind() LblCount.Text = numRows LblSum.Text = sumRows numRows is the number of records in a particular category. CountQuery is a method that returns the total number of records in all the categories. I don't know how to get the result of that query, the code line in bold isn't right. How do I get this number?Diane
View 5 Replies View RelatedSelect Count(*) For Getting # Of Records
Nov 23, 2007Hello,
I am writing a piece of code in ASP.NET and I'd like to get the # of records on a table and used this code:
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='G:Aco ProntoBSCBSC_v1.mdb'"Dim Con As New OleDbConnection(ConnString)
Dim Cmd As New OleDbCommand("SELECT COUNT(*) AS Expr1 FROM Metricas", Con)Dim reader As OleDbDataReader
Con.Open()
reader = Cmd.ExecuteReader()Dim NumMetr As Integer = Val(reader("Expr1"))
reader.Close()
Con.Close()
I am getting an error that that's no data in the table.
Any suggestions?
Get Records With A Count Total
Feb 14, 2008Hello, I am having problems with this query below:
1 SELECT Table1.Email AS Email,
2 Table2.UserName AS Username,
3 Table3.Members_Paid AS Paid,
4 (SELECT DISTINCT COUNT(*)
5 FROM Table3 AS e JOIN Table3 AS m
6 ON e.Members_Sponsor = m.Members_ID
7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords
8 FROM Table1 INNER JOIN
9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN
10 Table3 ON Table2.UserId = Table3.UserID
11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
How To Count Number Of Records
Feb 26, 2008hi
can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.