Hi,
I need to get the groupwise distinct count .
For eg. Lets assume we have group as Category and under this there are two childs Category1 and Category2.
I need the distinct count under Category1 and Category2 separately with the count resetting after Category1 and Category2.
Any help will be greatly appreciated.
Regards,
Ravi
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
--------------
returns
MON 6
TUE 3
WED 5
THU 3
FRI 2
SAT 1
Hi all,
i am using sql server 2005.
let me explain the scenario.
i am writing a function that return no of call a sales execituve done.
an executive can present n no of product to one customer but that call will be only one
my table record look like
empid custid product date and other field
em111 iimran aaaa 01-04-2008 ........
em111 iimran bbbb 01-04-2008 ........
em111 iimran cccc 01-04-2008 ........
em111 xxxxxx aaaa 01-04-2008 ........
em111 xxxxxx 2222 01-04-2008 ........
em111 iimran aaaa 02-04-2008 ........
em111 iimran bbbb 02-04-2008 ........
em111 iimran cccc 02-04-2008 ........
em111 xxxxxx aaaa 02-04-2008 ........
em111 xxxxxx 2222 02-04-2008 ........
Now if i select total no of call it should give me four call
also i am getting this through this query
select dcr_date , cust_id from vwdcrdtl where emp_id = @empcode and dcr_date between @stdate and @enddate group by dcr_date , dcr_cust_id
this query correctly return 4 rowcount(which i am gerring though @@rowcount) but i want this rowcount to return from function
i assinged this to rowcount to int veriable but this giving me error .
i would like to know how can i get count of these records
Please Help
Hi Folks,
Please help me in writing a query. I need to get current strength of employee's, monthwise (till each month).
Table Structure
----------------
Employee_Master
ED_EmpId ED_DOJ ED_Name
-------- ------- ---------
2006-01-02 SRINIVASA
2006-01-02 KAVITA
2006-01-02 VIVEK
2006-02-20 CHANDRA
2006-02-25 PARIMAL
2006-02-30 SATISH
........
2007-01-10SANJEEV SHARMA
2007-01-14 JITENDRA PRATAP SINGH
2007-03-15 SANDEEP
2007-03-02 SUNIL KUMAR SHARMA
I require the data in the below format..
For the year '2007-2008'
Month Count
----- -----
April(07) 10
May . 10
June . 10
July . 10
August . 10
Sept 10
Oct 10
Nov 10
Dec 10
Jan (08) 10
Feb . 10
March . 10
Thanks.
Hi All,I want to give sequence no group wise, as follows:Section EmpCode-----------------------A 1001A 1003A 1009B 1023B 1030C 1044-----------------------I want to set sequence no. group wise like this for above data.Seq. Section EmpCode------------------------------1 A 10012 A 10033 A 10091 B 10232 B 10301 C 1044----------------------------Suggestion in this regard will be appreciatedThanks in Advance,T.S.Negi
View 3 Replies View RelatedI have a group and a subgroup in a report. in the subgroup there are repetitive rows. But i want to display them at only once within the subgroup. So I hide them. Now I want to have the sum of a field viz. "quantity" group wise, having the only single value of the subgroup. But problem is in the sum the values which are hidden also included. But i don't want them to include in the sum of the group.
Can anybody give the suggestions how to resolve the problem..
I am fighting with it from a long back but i am not able to solve the issue.. plz help me out..
location invoice no. date quantity item
delhi inv/del/1 1/1/2008 3 item-1
item-2
item-3
inv/del/2 2/1/2008 2 item-4
item-5
Location Total 5
This is what i want... for invoice inv/del/1 there are 3 items, i hide rest of two rows using "Hide Duplicates", But the group sum i.e. Location Total in the report is coming out as '13' as it includes the quantities of hidden rows also. But in actual I want '5'.
thanx you..
I am trying to get count of records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)
Output
Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139
is there any more efficient way for example to implement the next query?
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
WHERE dealer = 'dealer sample'
What indexes I should create for this query?
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
How to insert a row number for a zone wise(ie group by zone column) in ssrs report in zone column i should get zone1 only once (should not get Zone1,zone1, zone1 -3 times)
sl.no Zone District no.of.region
1 hyd 24
2 ZONE1 chn 12
3 bang 2
1 raj 4
2 ZONE2 vizag 3
3 bbb 34
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
hi!
i am trying to get a count of 3 distinct values , one of them being a datetime - am running into errors - any suggestions are appreciated.
select count(distinct individualid + intakeseq + exitdate)from #temp
i am trying to do a distinct on individualid + intakeseq + exitdate,
& then get their count.
Thanks!
Hello
for mS SQL 2000
with
SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region
I get 254 rows
but how can I get the COUNT only ?
something like
SELECT COUNT(SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region) AS CPT FROM Groups
i must get 254
thank you for helpings
hi all,
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
will return me
DocrefId Count
DC01C060272
when i expect
DocrefId Count
DC01C060271
~~~Focus on problem, not solution~~~
Hi,
I have write down following query ..
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
Reply soon
Hi
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).
Can some body help?
Thanks in Advance!
Santhosh
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.
View 2 Replies View Relatedhi, 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
View 3 Replies View RelatedHi,
I want a count of distinct rows in a table through a single query -- is it possible?
eg.
table-
create table ch1 (a int, b int, c int, d int)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)
Here distinct row count in a table is 3 which I want to achieve thro a query.
if I do
select count(distinct a) from ch1 it works fine and gives me output as 2.
but this is not working
select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??
Please reply.
Cheers!
Ram.
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)
Sham
how can i count in sql the number or records taht would be returned if i did
select distinct site,date from allrecords
I am looking for a document or article that explains when it would be best to use a GROUP BY, or DISTINCT.
Cheers,
Casey
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 need a query to return the number of distinct call_num(s) between two timestamps.
For example search between 2008-04-17 05:00:00.000 and
2008-04-19 05:00:00.000
Calls
----------
id_tag(unique id) | status | call_num | entry_id(timestamp)
1 HOLD 0123456789 2008-04-17 05:07:00.080
2 ONSCENE 012345679 2008-04-17 05:10:00.012
3 ENROUTE 321654987 2008-04-19 04:00:00.000
so the sample answer would be : 2
Aim- Need to count how many [FDMSAccountNo] fall under a unique parentid
This is my query
SELECT [FDMSAccountNo]
,ParentID
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878028271889'
Which produces the following, The number of fdmsaccountno under parentid is two. Its two because one of the fdmsaccountno is = to parentid
FDMSAccountNo ParentID
878028271889878028271889
878028272887878028271889
878028273885878028271889
Desired result
Parentid #_of_outlets
878028271889 2
Hello,
I'm fairly new to SQL as so am looking for help with an ad hoc query. The data in focusing on is from table named APs:
FileNo Type PartNo Completed
6 Northbound 1 03/03/2007
6 Northbound 2 NULL
6 Other 1 NULL
6 Other 2 NULL
20 Proof 0 19/07/2006
20 TCP 0 21/07/2006
24 40-Day 1 16/01/2006
24 40-Day 2 16/03/2006
24 Other 0 NULL
44 Northbound 1 16/01/2006
44 Northbound 2 16/06/2006
44 Northbound 3 16/12/2006
44 Northbound 4 01/01/2007
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]
Total count should be: 4
I want to build query to return how many rows are in this query:select distinct c1, c2 from t1But SQL won't accept this syntax:select count (distinct c1, c2) from t1Does someone know how to count multiple distinct columns? Thanks.--Disclaimer: This post is solely an individual opinion and does not speak onbehalf of any organization.
View 3 Replies View Related