Counting Multiple Occurance Sql
Feb 22, 2008
Declare @MYTable Table
(
RecordID int identity,
EmployeeID int,
JobCode varchar(4)
)
Insert into @MYTable Values(1,'123')
Insert into @MYTable Values(2,'123')
Insert into @MYTAble Values (1,'123')
Insert into @MyTable Values (2, '123')
Insert into @MyTable Values (3,'123')
Insert into @MYTable Values (1,'222')
Insert into @MYtable Values (3,'222')
Insert into @MYTable Values (1,'222')
Insert into @MyTable Values (4,'222')
Insert into @MyTable Values (5,'123')
Insert into @MyTable Values (6,'123')
Insert into @MyTable Values (7,'457')
Select * from @Mytable
The each employee can come to this table multiple time however
the job code should be different for each every single record.
So If I have to check this error and see which record has been repeated with
the same Employee ID and JobCode and how many times. how would i do that
Thanks,
View 15 Replies
ADVERTISEMENT
Feb 19, 2013
I have a large table that tells me customers and mailings (~4 million rows). So for each customer, I see what mailing(s) they received. It is at the mailing level so if a customer received 2 mailings, their will be 2 records for that customer number, one for each mailing they received. There are 3 different mailings.I am trying to calculate the count of customers that received all 3, as well as those that received 1&2 only, 1&3 only, and 2&3 only. For example:
CustNo Mailing
Tim 1
Tim 3
Dan 1
Dan 3
Jane 1
Max 1
Max 2
Max 3
Lauren 2
Lauren 3
Results:
All 3 - 1
1&2 only - 0
1&3 only - 2
2&3 only - 1
I have tried doing some funky table joining and EXISTS, but the query runs and runs because of how much data it has to sift through. Here is the code I am using to find Mailing 1&2 only customers, but I am not even sure it is getting me the right numbers. There are 2 different types of customers as well which is why I have custtype in there.
Code:
select a.custtype,count(distinct a.custno)
from myTable a
inner join (select distinct custno from mytable where [mailing]=2) b
on a.custno=b.custno
where a.[mailing]=1
and not exists(select * from myTable c where a.custno=c.custno and c.[mailing]=3)
group by a.custtype
View 14 Replies
View Related
Jul 14, 2014
Consider the following dataset:
ID|MD|TYPE
1|JOHN|A
2|JOHN|B
3|JOHN|B
4|BOB|A
5|BOB|A
6|BOB|B
7|BOB|B
8|BOB|B
I need to count the number of IDs for each MD and each TYPE like this:
MD|A|B
JOHN|1|2
BOB|2|3
I only know how to count everything by MD like this:
SELECT MD, COUNT(ID) AS TOTAL
FROM MY_TABLE
GROUP BY MD
ORDER BY MD
The query above results in:
MD|TOTAL
JOHN|3
BOB|5
View 5 Replies
View Related
Jul 19, 2007
am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))
but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))
P.S i want to be able to use it in a where clause :
select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp)
thanks in advance
View 8 Replies
View Related
Mar 26, 2008
Hi!
I have a problem with a sql script.
This little nice script works fine by itself.
select top 1 MWh from
(
select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID
) as [Elleverantor 2 MWh]
But when I put in my long select it doesnt work. Also the long script works fine without these rows inserted. I hope someone can tell me why.
//Tommy
I post the entire script and the error message can be found below.
Msg 156, Level 15, State 1, Line 59
Incorrect syntax near the keyword 'select'.
Msg 170, Level 15, State 1, Line 114
Line 114: Incorrect syntax near '-'.
SELECT tDeklaration.Diarienummer,
IO.OrgNr,
IO.OrgNamn,
tDeklaration.KontaktpersonNamn,
tDeklaration.KontaktpersonEpost,
tDeklaration.KontaktpersonTele,
(select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID) as [Egen producerad],
-- Hämta ut Orgnamnet för en Elleverantör. Finns det inte i Tabellen ta intressentID:t och hoppa iväg till IREG.
(select
Case When
(Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL
then
(Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNamn from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID =
(SELECT top 1 IntressentID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL ))
ELse (Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 Namn],
(select
Case When
(Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL
then
(Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNr from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID =
(SELECT top 1 IntressentID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL ))
ELse (Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 OrgNr],
(Select MWh from tDeklarationElleverantor Where DeklarationElleverantorID =
(SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor
WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) as [Elleverantor 1 MWh],
-- Here is the problem.......
select top 1 MWh from
(
select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID
) as [Elleverantor 2 MWh],
(select (select sum(tDeklarationElleverantor.MWh) from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID)
+ (select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID)
) as [Summa använd och vidare fakturerad],
ElTillverkning AS Tillverkningsprocess,
ElDel AS [Tillverkningsprocess del av år],
ForsaljningsVarde AS Försäljningsvärde,
(select
Case When ElTillverkning > 0 or Forsaljningsvarde > 0 Then ElTillverkning/Forsaljningsvarde
ELse 0 End) As Kvot,
(select
Case When ElTillverkning <= 0 or Forsaljningsvarde <= 0 Then 0
When ElTillverkning/Forsaljningsvarde < 40 then 0
When ElTillverkning/Forsaljningsvarde >= 40 and ElTillverkning/Forsaljningsvarde < 50 then ElTillverkning/Forsaljningsvarde * 0.5
When ElTillverkning/Forsaljningsvarde >= 50 and ElTillverkning/Forsaljningsvarde < 60 then ElTillverkning/Forsaljningsvarde * 0.75
ELse ElTillverkning/Forsaljningsvarde End) as [Undantagen elmängd],
(select
(select SUM(tHjalpkraft.Bruttoproduktion * tSchablonAr.Schablon)/100 from
tHjalpkraft INNER JOIN tSchablonAr ON tHjalpkraft.KraftslagID = tSchablonAr.KraftslagID
where tHjalpkraft.DeklarationID = tDeklaration.DeklarationID and tSchablonAr.Ar=2007)
+
(select SUM( tFaktiskHjalpkraft.NettoProduktion) from tFaktiskHjalpkraft where tDeklaration.DeklarationID = tFaktiskHjalpkraft.DeklarationID)) AS [Hjälpkraft],
--Summa avdrag för vidare fakturerad el (ny kolumn, summan under rubrik 5)
(select SUM(tFornybarFrom07.Fornybar) from tFornybarFrom07 where tFornybarFrom07.DeklarationID = tDeklaration.DeklarationID) AS [Förnybar],
tDeklaration.Kvotplikt,
tDeklaration.BegartAnnullerat as Annullering,
tDeklaration.Ovrigt as [Övriga Upplysningar],
tDeklaration.Kvotplikt - tDeklaration.Annullerat AS Saknas,
tDeklaration.DeklarationStatusID,
tDeklarationStatus.StatusIntern
FROM tDeklaration
INNER JOIN tDeklarationStatus on tDeklaration.DeklarationStatusID = tDeklarationStatus.DeklarationStatusID
INNER JOIN tRegistreradKvotpliktIntressent ON tDeklaration.RegistreradKvotpliktID = tRegistreradKvotpliktIntressent.RegistreradKvotpliktID
INNER JOIN tIntressentforadOrg ON tIntressentforadOrg.IntressentforadOrgID = tRegistreradKvotpliktIntressent.IntressentForadOrgID
LEFT OUTER JOIN intressentregister_skarp.dbo.tIntressentOrganisation IO ON IO.IntressentID = tIntressentforadOrg.ForadAgarID
--left outer join tDeklarationElleverantor on tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID
WHERE (tDeklaration.AktuellIntern = 1) AND (tDeklaration.KategoriID = 3) AND (tDeklaration.DeklarationStatusID <> 1) AND
(tDeklaration.DeklarationStatusID <> 3)and tdeklaration.kvotpliktar=2007
order by IO.OrgNamn
View 4 Replies
View Related
Sep 20, 2004
Hey all:
I have a table that contains the following records:
ptrecidpaidbyamtchequenoname
4791A X A1200097760LOWE, Bernard
4791A X A4380097760LOWE, Bernard
4791A X A620106406LOWE, Bernard
I need to create a view that returns only the FIRST occurance of each distinct ptrecid. I do NOT want to sum amt/chequeno, but rather return only the values in the first record. Using a group by gets me close, but since the amt and chequeno are not unique, they do not group. Min also does not work, as 620 is returned for amt, and 097760 is returned for chequeno.
The desired result from this query would return:
4791A X A1200097760LOWE, Bernard
Any help on this is greatly appreciated.
View 6 Replies
View Related
Sep 18, 2001
I am trying to create a view that shows the first occurance of a particular value in a column.
For example, I have a table that contains multiple part numbers that can be associated with mulitple customers. The first occurance of the part number in the table is associated with the main customer for that part number. I want this view to only show the main customer for each part number.
I am able to do this in Access using the following SQL statement:
SELECT dbo_CustPattMast.PATTERN_NUM, First (dbo_CustPattMast.CUST_NUM)
FROM dbo_CustPattMast
GROUP BY dbo_CustPattMast.PATTERN_NUM
When trying to create the view in the SQL Manager, it will not allow me to use the First statement.
Thanks in advance for your help,
Jennifer
View 4 Replies
View Related
Sep 23, 2006
I am making a sql query and its bring back hundreds of results but I only need the first one. I am aware that getting the first one will be faster as well. Is there an efficient way to do this?
View 2 Replies
View Related
Mar 20, 2008
Is there a simple command to find the 18th occurance/position of ";" in a field and I need to do this in the SQL code.
I can use charindex to find the 1st position and I can write a function to keep chopping off the data at the start of the field until I get to the 18th occurance but it takes 18 lines or so of code.
Is there a simpler way of doing this?
I need to find the 18th occurance of ";" and then take the next 4 characters.
View 4 Replies
View Related
May 17, 2008
Hi friends,
I have a two table with following fields, table names are tbl_userinfo, tbl_Property.
tbl_userinfo fields are
user_id name
1 dhin
2 Mike
3 sam
4 Red
tbl_Property fields are
prpty_Id User_Id Address
1 1 3CostalRoad
2 1 westbengal
3 2 Loasass
what i want to do is, if tbl_info User_id occures in tbl_property, i want to display that full info abt tbl_userinfo
after comparing two tables Expected result is
user_id name
1 dhin
2 Mike
Please help me how to do this
View 6 Replies
View Related
Sep 16, 2004
This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
View 2 Replies
View Related
Dec 14, 2000
Hello all
my problem is like this
I have a windows nt server 4.0 .its having IIS4.0 as well as Ms sql server 7.0 installed. I am using ASP ( active server pages) to store the fields entered by the customers in the order processing form in my site( thats the default page--form)
this form calls the asp file and asp file successfully stores the fields in the corresponding table.Now the problem is that how can i configure my sql server in such a way that sql server can mail me( as i am the administrator) informing the new order placed by the customer.
I know sql mail , and i have already configured that also. but how can i create the trigger for the table updation?
i tried using the enterprise manager, i was using xp_sendmail. but sql says "the stored procedure can't be found"
so anyone who knows this topic , respond please
Regards
Shabu
View 1 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
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
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
Jun 10, 2006
Hi All
I have:
select prod_id , count(prod_id)[Number of Times]
from orders_tbl
group by prod_id
order by 1;
I get a result however, how do sum up the total number of times ?
Thanks
View 5 Replies
View Related
Aug 18, 2006
Hello all.
I am looking to select all from a particular row where one field occurs more than once. The code i'm, trying isnt working:
select * from U_DATAFILE
where count(group by VIEW_ACTION)>1
Thanks all :)
View 10 Replies
View Related
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