INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
I have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:
ColumnName DistinctCount NormalCount Id 1000 1000 Name 1000 1000 Phone 600 600 PINCode 200 1000 City 400 1000 Gender 2 1000
I have a CTE returning a recordset which contains a column SRC. SRC is a number which I use later to get counts and sums for the records in a distinct list.
declare@startdate date = '2014-04-01' declare@enddate date = '2014-05-01' ; with SM as ( SELECT --ROW_NUMBER() OVER (PARTITION BY u.SRC ORDER BY u.SRC) As Row, u.SRC,
[Code] ....
-- If Referral start date is between our requested dates
ref.Referral_Start_Date between @startdate and @enddate
OR
-- Include referrals which started before our requested date, but are still active during our date range.
(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL )) ) INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id AND s.Service_Delivery_Unit_Id = 200 ) SELECT count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,
I would want to reduce that somehow to idcol1col2col3 ----------------------------- 1abc123null 1abc987null 4alphabetagamma
First thing in my mind was distinct over multiple columns, but I haven't found any resource mentioning that possibility. I don't immediatly see a way without (complex) looping. Can anybody put me on the right track?
btw, I know that Id is not an id. It's a table var that holds an id of a table combined with fields of other tables.
Hi all...I have a table in which some columns has distinct values and some hasduplicates..i wan to select all the columns with distinct values....noproblem if rows has null value in it....i tried a lot wit distinct andgroup by but nothing got worked out...Waitin for your reply.....Thanking you...
I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/10/2005 12:00:00 | 23489 | 10/15/2006 15:13:23 |Desired Result: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 23489 | 10/15/2006 15:13:23 |Here is the query I'm working with... just can't figure out how to join or limit the results to ONLY unique matches in Acct Number AND DateOfService. "SELECT tblCH.ProcedureKey AS CPT, tblPC.Description, DATEDIFF(d, tblPat.BirthDate, " & _ " { fn NOW() }) / 365 AS Age, tblPat.LastName, tblPat.FirstName, tblPat.BirthDate," & _ " CAST(tblCH.AccountKey AS varchar) + '.' + CAST(tblCH.DependentKey AS varchar) AS Account, tblCH.DateOfService " & _ " FROM dbo.Procedure_Code___Servcode_dat tblPC INNER JOIN " & _ " dbo.Charge_History___Prohist_dat tblCH ON tblPC.ProcedureKey = tblCH.ProcedureKey RIGHT OUTER JOIN " & _ " dbo.Patient_Info___Patfile_dat tblPat ON tblCH.AccountKey = (tblPat.AccountKey AND tblCH.DependentKey) = tblPat.DependentKey "Any suggestions from y'all SQL gurus? I have to have this report ready for production by tomorrow morning and this is the last fix I need to make =Thank you =)
I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.
I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.
COL1 | COL2 | COL3 | COL4 1 | FD | DR. A | Y 2 | FD | DR. A | Y 3 | FD | DR. A | N 4 | FD | DR. A | Y 5 | FD | DR. A | Y 6 | PF | DR. A | Y 7 | FD | DR. B | Y 8 | PF | DR. B | N
Consider the script below:
SELECT COL2, COL3, COUNT(COL1) AS TOTALS FROM CASES GROUP BY COL2, COL3 ORDER BY COL3, COL2
The script above produces the following output:
COL2 | COL3 | TOTALS FD | DR. A | 5 PF | DR. A | 1 FD | DR. B | 1 PF | DR. B | 1
I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:
COL2 | COL3 | TOTALS | NEWCOL FD | DR. A | 5 | 4 PF | DR. A | 1 | 1 FD | DR. B | 1 | 1 PF | DR. B | 1 | 0
I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.
1 2015 ba1 137 HL EL Eco 2 2015 ba1 138 EL SL HS 3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS 2 15120002 2015 ba1 HL EL Eco 3 15120003 2015 ba1 SL EL His 4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
sometimes, when tables are poorly joined, we have exactly same rows as a result.. so we use distinct.. but how can we count(*) the distinct rows?
select distinct d.docrefid from tbljobDocuments d left join tbljobitems j on j.docrefid=d.docrefid where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'
will return me
docrefid DC01C06027
but when i try to count(*) only the distinct record, it count all..
select distinct d.docrefid, count(*) from tbljobDocuments d left join tbljobitems j on j.docrefid=d.docrefid where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700' group by d.docrefid
select a.patientid, a.providerid from PatientDrugList a JOIN Admin..Loadinstance b on a.intLoadInstanceId = b.intLoadInstanceId where a.MailReasonTypeId = -1 and b.SubClientId = 22 and b.StatusTypeId > 0
Now how could I get distinct patientid count and distinct providerid count
I have a table which stores the shift information for employees. The table contains 10 columns as Employeename,Employeeno,month,year,shifttimings etc. If an employee works a day in a particular shift, then a row will be inserted in to the above table for that employee.
Now at the end of the month i wanted to calculate the shift details for each employee for a particular month of a given year like employeename,employeeno, noofdays(countof shiftdays).
I have an SQL statement that looks like the following:
SELECT [Docs-Entities].entityID, entityName, COUNT([Docs-DocsEntities].filename) AS numDocs FROM [Docs-Entities] LEFT JOIN [Docs-DocsEntities] ON [Docs-Entities].entityID = [Docs-DocsEntities].entityID GROUP BY [Docs-Entities].entityID, entityName
but the problem is that numDocs (the COUNT) is not returning a distinct count. In the DocEntities table, a particular document can actually have multiple entries with the same entityID so that produces inflated numbers for numDocs. But when I do a SELECT DISTINCT on a particular entityID, the results are less and don't match the numDocs number because I only need to list the document one time. This is not a huge issue, but it looks bad on my site.
Is there a way that I can make COUNT count distinctly?
Thanks for the help and I hope I worded that cllearly...
I'm struggling to fin a way to use DISTINCT keyword with ROLLUP (or Cube).For example,SELECT employee_city, employee_country, COUNT(DISTINCT employee_name)FROM employeeGROUP BY employee_city, employee_country WITH ROLLUPthat query does not work.Is there a workaround?Thx.
hi, I have a table that contain(custname,ordno,ordchange,ordlocation,reaso n) I want to determine the following: how many orders per customer,how many orders changed per customer,how many order location per customer, what reason for change per customer,
here what I wrote as a query, tell me if I am right. I thank you for your help
select custname, COUNT(DISTINCT ordno) , COUNT(DISTINCT ordchange) , COUNT(DISTINCT ordlocation) , COUNT( reason) from customers a, orders b where a.custname =b.custname group by custname
helloi want to do only one query for :SELECT DISTINCT Name FROM UsersSELECT COUNT(Name) AS Names FROM Users WHERE (Name LIKE 'xxx')something like :SELECT Name, COUNT(Name) AS Names FROM Users WHERE Name IN (SELECT DISTINCT Name FROM Users)i must get :Joe 23julie 17.....thank you
I have a table with following fields tdate custcode prodcode
table is filled with full year data and i want following result
I want count of distinct custcode in every past three months.
for example Result like this
month tjan tfeb tmar tapr tmay ..... tdec
prod1 prod2 . . prod5
And data under tmar should be count of distinct custcode of (jan,feb and mar) for corresponding prod code is required. Under tapr, count of distinct custcode of (feb,mar and apr) for corresponding prod code is required.
Can any1 help me please.
I am using MS SQL 2005 and above table is a big table (approx 10 million records)
hello, i have a working stored procedure SELECT CommentID, UserName, PictureID, DateCommented, COUNT(CommentID) OVER (PARTITION BY PictureID) AS 'NrOfComments' FROM Comments WHERE PictureID = @PictureID witch returns among others the number of comments for a picture i need to select the number of distinct users who commented that user too, so i added this at SELECT statement, COUNT(DISTINCT UserName) AS 'Expr1' i get that error: "Colum 'Comments.CommentID' is invalig in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause." what should i do, to select the number of distinct users who commented on a specific picture? here are the table rows, if you need that CommentID --- UserName --- PictureID --- DateCommented please help me, thank you
It doesn't seem possibly, but maybe? Is there a way to have an expression be used, but also benefit from using distinct on a column?
I'm looking for something like: sum(case when dtEntered > '1-1-2006' then 1 else 0 end) but also encorporating somehow a distinct count on UserName. So a username showing twice would only count once, and this would only be counted if the record's dtEntered date was greater than Jan 1, 2006.
The reason I'm writing the statement that way is because there are 5 columns which aggregate data by different time periods.
If it's not possible, I will just end up joining to the table multiple times, putting the date filter in the where clause.
I am trying to get the below query to work and can't seem to get past this error. Not sure but everywhere I look I think my syntax is right. I am probably missing something obvious but I'm just back from vacation and trying to get in the swing of things. Any help would be appreciated.
Select Distinct(chadcd) as Adjustor, Count (Distinct chclno) as NumberOfCases, Count (Distinct chclno,chwkno)as NumberofClauses from clmhdr where chpddt = '20040630' Group By chadcd Order by Adjustor
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ',chwkno'.
I've tried variations on a SELECT statement like below but have been unable to find a way to count only those types per fileNo that have all partNo completed (and to count all types per fileNo with a partNo of 0 and a completed date as they have no parts):
SELECT [FileNo], COUNT(DISTINCT [Type]) AS CountOfAPs FROM APs WHERE (completed IS NOT NULL) GROUP BY [File]
I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN
0 "
I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.
------------------------------------ SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek, COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration WHERE Archived = 0 GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3)) ORDER BY CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7 END
Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.
I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.
My report would produce the following output considering the above dataset:
Merchandise Number of customers who used a coupon -------------------------------------------------------------------------------------------------------------- Department 1 2
Class 1 2
Barcode 123 2 Barcode 789 1 Department 2 0
Class 7 0
Barcode 456 0
I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.