Counting Occurrences Of A Value
Jul 19, 2007
I have a column in a report with values Y or N or V. In the header, I have to show the number of times each value appeared.
I used RepeatingValue() with IIF() but it's counting all the values as same, e.g., if there are four occurrences of Y, two of N and one of V, I want something like this:
Y Cnt=4, N Cnt=2, V Cnt=1
My assumption was that this will work (white spaces added for readability:
=iif(
Fields!myCol.Value = "Y",
"Y Cnt=" & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
iif(Fields!myCol.Value = "N",
"N Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
iif(Fields!myCol.Value = "V",
"V Cnt= & RunningValue(Fields!myCol.Value, Count, "dsMyDataSet"),
"NULL"
)
)
)
Please help.
View 3 Replies
ADVERTISEMENT
Sep 23, 2015
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
View 9 Replies
View Related
Oct 27, 2003
Finding numbers of occurrences of a string of characters
in a column of TEXT datatype.
DDL of involved table txt:
create table txt (pk int, txtcol text) -- datatype of pk doesn't matter
declare @word varchar(80) set @word='help'
declare @pk int, @count int, @i int, @dl int, @wl int
set @wl=len(@word)
declare abc cursor for select pk from txt
where patindex('%'+@word+'%',txtcol)>0 order by pk
open abc fetch next from abc into @pk
while @@fetch_status=0
begin
select @dl=datalength(txtcol) from txt where pk=@pk
select @i=patindex('%'+@word+'%',txtcol)+@wl from txt where pk=@pk
set @count=1
while @i<@dl
begin
select @count=@count+(len(substring(txtcol,@i,8000))-
len(replace(substring(txtcol,@i,8000),@word,'')))/@wl
from txt where pk=@pk
set @i=@i+8001-@wl
end
select pk=@pk, occurrences=@count
fetch next from abc into @pk
end
close abc deallocate abc
pk occurrences
----------- -----------
1 1
pk occurrences
----------- -----------
2 2
pk occurrences
----------- -----------
3 11
Edit: as suggested-reminded by jsmith8858.
View 7 Replies
View Related
Jun 19, 2007
If I want to return records where a column has similar data and occurs more than once what is the function?
Ex. If a database contains:
Column 1 Column 2 Column 3
1 Dog White
2 Cat Brown
3 Dog Black
4 Mouse Black
5 Cat White
and I want to show all records where the 2nd column has more than one occurrences so that I get both records where Dog appeared and both records where Cat appeared (since each appeared more than 1 time) - what do i need to write as my function?
View 4 Replies
View Related
Jul 25, 2006
Hi,
I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate
I have a details table where I capture the following details
CustID, DateofPurchase, PurchaseDetails
I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.
Thanks for your help in advance.
Regards
Dinesh
View 2 Replies
View Related
Oct 4, 2012
I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.
create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO
EXPECTED RESULT:
1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS
drop table t1
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
View 9 Replies
View Related
Jun 1, 2008
I have a table as below:
COMPUTERNAME, COUNTER, REASON
WXP-13Failed
WXP-1113Failed
WXP-38Failed
WXP-910Failed
WXP-117Success
What I would like to do is create a percentage of Success vs. the Failed for the same Computername.
Problem is when I use Group by I cannot tell the COUNTER of the Success anymore. This is what I have been using:
SELECT COMPUTERNAME, SUM(COUNTER) AS COUNTERTOTAL
FROM
ReturnTable
GROUP BY COMPUTERNAME
Thank you. The above query actually targets a result table from another query, but that should not matter.
View 11 Replies
View Related
Aug 18, 2013
I want to retrieve staff who attend less than 80% for a meeting type assuming we have 10 meetings per list.
Meeting Table:
staffID list date
------------------------
1 A 2013-01-15
2 B 2013-01-17
1 B 2013-01-17
1 A 2013-01-18
2 B 2013-01-19
1 A 2013-01-20
2 C 2013-01-21
* 1 - Dan
* 2 - Jane
When the staffID occur 3 times (70%), query will Output:
staffName list Participation%
------------------------
Dan A 70
SELECT a.staffName, b.list,
(100 - ((COUNT(c.staffID) * 100) / 10)) AS 'Participation%'
from Staff AS a, listType AS b, Meeting AS c
where a.staffID = c.staffID AND
b.list = c.list
GROUP BY a.staffName, b.list
HAVING COUNT(c.staffID) > 2
View 2 Replies
View Related
Sep 9, 2015
I am working with a pretty complicated xml document but I am only trying to grab specific information.
The issue I am having is that the info I am after is in a node>parent>sibling where there are similar repeating siblings.
<ClinicalDocument xmlns="urn:hl7-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="1.2.840.114350.1.72.1.51693" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<templateId root="2.16.840.1.113883.10.20.22.1.2" />
<id assigningAuthorityName="EPC" root="1.2.840.114350.1.13.291.2.7.8.688883.87504" />
[code].....
Here is my query:
WITH XMLNAMESPACES(DEFAULT 'urn:hl7-org:v3')
SELECT t.document_id,
t.person_id,pref.value('title[1]', 'varchar(255)') AS Title,
[Code] ....
This almost gives me what I need but I am only concerned with, in this case, the first sibling component but it is also picking up Information from the second. in this case it is picking up the caption containing "Strep A Antigen Scrn, Cult if Indicated (09/07/2015 6:35 PM EDT)" Also the number of components siblings change from document to document and although in this example I am trying to get the first component sibling, in actuality the component sibling is more towards the bottom.
Is there a way to only grab the info under the <title> in the component sibling I am after?
It will always be <title>Visit Diagnoses</title>. Is there a way to pinpoint this in the above query? Or am I going at it all wrong?
View 2 Replies
View Related
Jan 24, 2008
I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:
NNNNNRJKSURNNNEJNNNN
Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?
View 7 Replies
View Related
Nov 5, 2015
I am trying to build a query to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates. All of the information needed is in the same db. How do I do this?
Select db1.customer_id,
db1.product_id,
db1.purchase_date
From db1
View 20 Replies
View Related
Oct 13, 2014
In my database I am adding up the occurrences of reports on days of the week. If I am not using the MAX value then I get something returned such as
EmployeeID DaysOfWeek ReportID
1001 1 201
1001 2 201
As I just need the highest value returned I'm attempting to use MAX. However, the problem is that all values are being returned in the DaysOfWeek column as 5 even though they may range anywhere from 1-7. The DaysOfWeek should be for a unique EmployeeID/ReportID combination.
WITH sub AS(
SELECT Shifts1.EmployeeID, X.*, Schedule.ReportID
FROM
(
SELECT
CASE WHEN [M] = '1' THEN 1 ELSE 0 END +
[Code] .....
View 8 Replies
View Related
Oct 21, 2015
How can we get the result set as TESTING1,TESTING2 from below
DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
Basically i need to get all the substrings which are in between $I10~ and $
View 6 Replies
View Related
Nov 18, 2015
I successfully used the query below to identify customers that purchased the a specific product (e.g. db1.product_id = '123') on different dates. Now I need to only pull the purchases from a particular region (client_cd = '593') that purchased a particular product on different dates. How can I do this?
select distinct T.* from db1 T1
where exists (select 1 from db1 T2 where T2.CustomerId = T1.CustomerId and T2.ProductId = T1.ProductId and T2.PurchaseDate
<> T1.PurchaseDate) and T1.ProductId = '123'<o:p></o:p>
View 3 Replies
View Related
Jun 3, 2015
I have a fact -REVENUE table of accounts, each account can have multiple instances . And I have an Aggregate that summarize the latest occurrence of a revenue for each account ( in a chosen Period ) :
AccountLastRevenue:=SUMX(
VALUES('scd_FactAccountRevenue'[Account]),
CALCULATE(
SUM('scd_FactAccountRevenue'[Revenue]),
LASTDATE('scd_FactAccountRevenue'[Revenue_Date]) )
)
How can I find the percent of one of the lastest accounts Compared to all accounts? Assuming i have connect Dim_Time ( Y-Q-M-D ) to Revenue_Date, how can i find the percent of one Month Compared to all the months in the Quarter ( And so on hierarchies ) ?
scd_FactAccountRevenue:
powerpivot :
View 11 Replies
View Related
Nov 14, 2014
Where can I find dates and times to when an availability group was moved outside of the SQL error log?
View 1 Replies
View Related
Aug 6, 2015
I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And I need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
View 5 Replies
View Related
Jun 26, 1998
When I have made a query with IDC, how can I make a counter in the .htx-file, which tells me if there was no query-results ?
View 2 Replies
View Related
Jan 28, 2007
Hi,New to SQL. Got some questions about it.Suppose I have two tables. Each of them has a single column, named asc1. For table T1, I have:11133579For table T2, I have:1234513The exercise I want to do is to select the number of occurence in T1for those elements in T2. For above tables, I want to show:1 3 ( i.e. "1" is in T2 and shows 3 times in T1)2 0 (i.e. "2" is in T2 but doesn't show in T1)3 2 (i.e. "3" is in T2 and show 2 times in T1)It seems I can't figure out a good way to do this. Any help will beappreciated.Thanks
View 2 Replies
View Related
Oct 24, 2007
I am working on writing a query to use with Reporting Services where I am pulling grades from a school grade book trying to see what students qualify for Honor Roll.
I have one Honor Roll done but am still working on the other. The qualifications I am trying to get is that a student has a gpa >3.0 and can have one C grade. I've got the 3.0 and the C's down but how to get the query to pull anyone having at the most one C. It doesn't matter whether it is C+, C, or C- but the student can't have more than a count of one.
Here is what I have
SELECT DISTINCT s.lastname+','+' '+s.firstname AS Student, s.studentnumber,e.grade,t.name AS Term,
gs.score, c.name AS Course, se.teacherdisplay
FROM GradingScore gs
INNER JOIN student s ON s.personid = gs.personid
INNER JOIN v_TermGpa tg ON tg.personid = s.personid
INNER JOIN enrollment e ON e.enrollmentid = s.enrollmentid AND e.calendarid = gs.calendarid
INNER JOIN scheduleStructure ss ON ss.calendarid = gs.calendarid
INNER JOIN Termschedule ts ON ts.structureid = ss.structureid
INNER JOIN Term t ON t.termscheduleid = ts.termscheduleid
INNER JOIN section se ON se.sectionid = gs.sectionid
INNER JOIN course c ON c.courseid = se.courseid
WHERE gs.calendarID = 20 AND t.name ='1st 6wk' AND tg.term1gpa >=3.0 AND s.enddate IS NULL
AND gs.score <='C-'
AND (select gs1.personid, gs1.score
from gradingscore gs1
inner join enrollment e1 on e1.personid = gs1.personid
Inner join schedulestructure ss1 on ss1.calendarid = e1.calendarid
Inner join termschedule ts1 on ts1.structureid = ss1.structureid
Inner join term t1 on t1.termscheduleid = ts1.termscheduleid
where gs1.score IN('C','C+','C-') and (count(score)= 0 or count(score) < 2))
ORDER BY s.[student]
I can get it to work all the way up to where the And (count(score) starts then there is something about the way the count is in there that I haven't figured out yet.
Thanks in Advance for any help or advice
View 9 Replies
View Related
Jan 26, 2008
Hi,
How do I count the total number of each row in a table? The table Company has a field called Group. How do I count the total amount of members in each group?
Thanks in advance!
View 23 Replies
View Related
Oct 5, 2007
Hi all,
I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.
So if I for example select all Dates older than today's date, I would want something like this:
1
10/12/2006
2
10/18/2006
3
10/20/2006
4
10/22/2006
5
10/30/2006
Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".
Is this possible? And if yes, how ? :)
Thanks in advance
Nick
View 6 Replies
View Related
Jan 31, 2008
Sorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing
View 2 Replies
View Related
Mar 15, 2004
Hi everyone,
another problem:
I'm trying to count the number of rows but it's not working. Here's my code:
SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites]
FROM [Activities]
WHERE [Person ID] IN
(
SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people)
FROM [Activites]
)
As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out.
Any help?
Andrew
View 3 Replies
View Related
Jun 22, 2004
Hi,
I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:
SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1
This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?
Cheers.
View 6 Replies
View Related
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
Aug 17, 2006
Hi,
How can I aggregate a top 5 count across two satellite tables?
e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.
Any help or pointers would be a great help!
Thanks.
View 3 Replies
View Related
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
Nov 29, 2006
Hi. I have a small problem that i just can't seem to figure out. I'm trying to generate a report for a case management system. The problem I am having is trying to exclude some of these results. Here is my query:
SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey
WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55
AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9
This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.
now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!
View 3 Replies
View Related
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
Mar 27, 2004
hiya all,
I got a table that is represented as a B-Tree, it has a one to
many relation with its self
------------
| UserID |
------------
|FatherID|
------------
UserID = FatherID
it looks like this
o
/// \
ooo ooo
//\ ///\
oo ooo ooo
I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it.
any ideas ? the DB isn't filled with data yet so we can add more fields
to the table if it helps.
I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on.
regards
Ahm
View 7 Replies
View Related
Apr 23, 2008
I have a table that I want to use for reports that holds these datatypes
safe: Integer
unsafe: Integer
made_safe: Bit
unobserved: Bit
Which gets populated.
I want to count the values in each field but Bit fields seem to be awkward. This is my attempt
SELECT
SUM(safe)as TotalSafe,
SUM(unsafe)as TotalUnsafe,
COUNT(Case WHEN unobserved = 1 THEN 1 ELSE NULL END)as NotSeen,
COUNT(Case when made_safe = 1 THEN 1 ELSE NULL END) as TotalMadeSafe
FROM myTable
Any ideas please
View 3 Replies
View Related
Jun 4, 2008
Hi all,
I am trying to count the total amount of times the primary key is used within each month of the year "JAN", "FEB" "MAR"... Unfortunately I have no idea how to A) Search by month and B) Count the total amount of times data within the new table that is created AFTER the outer joins are done. I hope you can help.
Thanks.
SELECT
*
FROM
tableA ta,
tableB tb,
tableC tc
WHERE tc.updated_date >= TO_DATE ('01/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS')
AND tc.updated_date <= TO_DATE ('31/05/2008 00:00:00','DD/MM/YYYY HH24:MI:SS')
AND tc.primaryKey = tb.primaryKey (+)
AND tc.primaryKey = ta.primaryKey (+)
AND count(tc.priarykey) -- This is what I want to count
;
View 2 Replies
View Related
