Count By Distinct Value When All Parts Not Null

Mar 12, 2007

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

View 9 Replies


ADVERTISEMENT

Distinct Count With Null Values (grand Total)

May 9, 2007

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of pulications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
--------------
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 1 Replies View Related

Distinct Count With Null Values (grand Total)

May 10, 2007

Hello,

I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
--------------
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
--------------
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
--------------
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
---------------------------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years


For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------------------- 30 --------------------------1----------------1
1070-------------------90 --------------------------2----------------2
1111-------------------0 ---------------------------1----------------0
1137-------------------0 ---------------------------1----------------0
1234-------------------1404--------------------------9----------------9
1721-------------------504--------------------------7----------------7
2661-------------------85 --------------------------5----------------5
...--------------------...---------------------------...----------------...
6999------------------- 20---------------------------1-----------------1
9956-------------------50---------------------------5-----------------5
Unknown----------------(empty)---------------------(empty)-----------0
Grand Total------------ 2421------------------------11-----------------11


Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?

View 2 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

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'

View 9 Replies View Related

Using Count With Distinct

Oct 23, 2001

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!

View 3 Replies View Related

Count From Distinct

Mar 14, 2007

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

View 4 Replies View Related

Count Distinct

Mar 23, 2007

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~~~

View 3 Replies View Related

DISTINCT AND COUNT

Nov 9, 2007

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

View 2 Replies View Related

Distinct Count

Jul 20, 2006

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



View 4 Replies View Related

SELECTing A DISTINCT COUNT?

Nov 30, 2003

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...

View 2 Replies View Related

COUNT(DISTINCT) With ROLLUP

Aug 17, 2005

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 Related

Distinct Count(fieldname)

Jan 28, 1999

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

View 1 Replies View Related

SELECT COUNT Of DISTINCT

Jul 19, 2006

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 Related

Distinct Row Count In A Table.

Aug 30, 2006

Hi,

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.

View 7 Replies View Related

Distinct Count Query

Dec 11, 2007

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

View 13 Replies View Related

Count Distinct Records

May 15, 2008

how can i count in sql the number or records taht would be returned if i did

select distinct site,date from allrecords

View 1 Replies View Related

I Get An Error When I Use COUNT(DISTINCT Colum_name)

Oct 14, 2007

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

View 5 Replies View Related

Distinct Count, But Rewritten With Sum(case..)

Mar 28, 2007

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.

View 3 Replies View Related

Ms Sql Select Count Distinct Probrem

Jul 12, 2004

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'.

View 1 Replies View Related

Using Count To Find # Of Distinct Entries

Apr 17, 2008

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

View 2 Replies View Related

Distinct Count Fall Under Unique ID

Feb 19, 2014

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

View 5 Replies View Related

Count Multiple Distinct Columns

Jul 20, 2005

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

COUNT (Distinct Column) = 0 With GROUP BY

Mar 20, 2008

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

View 4 Replies View Related

How To Conditionally Count Distinct Values?

Mar 17, 2008

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.

departmentId classId barcode orderId isDiscountedItem
----------------------------------------------------------------------------------------------------------------------
1 1 123 1 True
2 7 456 1 False
1 1 123 2 False
1 1 123 3 True
1 1 789 3 True
2 7 456 3 False
... ... ... ... ...

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.





View 3 Replies View Related

Distinct Count With Over Giving An Error

Nov 30, 2007

We have a table of students with fields for the ID of the school district and their school name. For example:



DistrictID
StudentName
SchoolName

10001
John Smith
Washington Elementary

10001
Jane Smith
Lincoln Middle

10002
David White
Hill High

...
...
...


I want is a listing with the school district ID, the student name, and the number of schools in the district.

I believe the following statement should give me what I want:

SELECT DistrictID, StudentName, COUNT(DISTINCT SchoolName) OVER (PARTITION BY DistrictID)

FROM StudentData


However, I get the error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'distinct'.

If I take out the DISTINCT it runs fine, however it counts all the rows as expected, which is not what I want.
If I remove the OVER clause it runs fine, however, it counds the schools across all the districts, which is not what I want.
I can remove the OVER clause and use GROUP BY, but not while also retrieving StudentName.


The SQL 2005 Online Books don't seem to indicate that my statement is invalid. Here are the links just for reference:
Count: http://msdn2.microsoft.com/en-us/library/ms175997.aspx
Over: http://msdn2.microsoft.com/en-us/library/ms189461.aspx

Anyone have an idea one what I'm doing wrong or how to go about getting this information?
Thanks!
Matt Penner

View 3 Replies View Related

Asking For Help With Distinct Count Within Rolling Period...

May 22, 2008

Thanks for taking the time to read my post. I greatly appreciate it!

What i'm trying to do is get a distinct count of account numbers within a rolling period. My actual take is rather large but i've created a smaller-like version below. Please reference this table.








Account
Date

1
1/1/08

2
1/2/08

3
1/2/08

2
2/8/08

4
2/9/08

1
2/15/08

1
3/5/08

5
3/6/08

4
3/9/08

3
3/10/08

1
4/1/08

5
4/9/08

2
4/15/08

3
4/26/08

1
5/3/08

2
5/15/08

3
5/29/08

6
5/30/08

Let's say i want to return distinct count of accounts within a 2-month rolling period meaning in February, i'd get a distinct count for accounts in January & February, then in March i'd get a distinct count for February & March, then in April i'd get it for March & April, and so on... my results table would like the table below:








Account
Month

3
1

4
2

5
3

5
4

5
5

I had asked this before but it was a summing equation and not a unique count. I've tried to play with the summing equation to kind of make it work, but i'm starting to get a headache. It's probably so simple!

Here's my previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2289509

View 8 Replies View Related

Analysis :: Distinct Count On Cube

Jun 12, 2006

i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

View 17 Replies View Related

DB Design :: How To Get Distinct Count Of Values

Sep 21, 2015

I have the below query,

select 
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column

[Code] ....

o/p: is :

LastEnforcementState NumberOfComputers PComputers

Compliant 7056 91.54
Downloading update(s) 39 0.51

[code]....

I want to add these two rows information into single row,

Failed to Download Updates(s)    131(127+8)       1.66(1.65+0.1)

how to do this in my sql query?

View 12 Replies View Related

How To Get Group Wise Distinct Count

May 9, 2008

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

View 4 Replies View Related

Employee Count By Two Distinct Indicators

Mar 11, 2008



This seems simple enough, I'm trying to get a count of employees by different indicators. First total by job code, second by Union_Code.

So I'm looking to count every one by distinct job codes inside a distinct Union_code first, then total the number for that distinct union.

Any help would be appreciated.




SELECT emp.job_code,
tbljob.job_title,
emp.union_code

FROM emp

INNER JOIN
empjob ON empjob.emp_id = emp.emp_id
AND empjob.company = emp.company
AND empjob.job_code_id = 'PRIM'

INNER JOIN
tbljob ON tbljob.job_code = empjob.job_code
AND tbljob.company = empjob.company

WHERE (emp.company = 'ABC')
ORDER BY emp.union_code, empjob.job_code

View 5 Replies View Related

Using Distinct And Count (*) Is Not Giving The Results Expected

Feb 12, 2008

I have 2 tables.  Product, ProductCategory.
Product table consists of (productid, productname, producttypeid, createdate, portid)
productcategory consists of (productid, categoryid, productcategoryid, categoryid, prodroletypeid, createdate)
I want to write sql query to get the unique products.  So the count should only give one value/resuls.  But when I run the following query, I am getting more than one rows
select count(distinct categoryid) as uniquecat
from productcategory as pc, product as p
where pc.prodroletypeid in ('P', 'F')
and pc.createdate <= dateadd(d, 30, getdate())
and p.portid = 100
group by pc.productid
 
 

View 3 Replies View Related

Query Help, Get Count Of Names Having Distinct Letters

May 8, 2007

here is a query


Code:

select count(d) from (select distinct left(name,9) as d from bscs where region like 'n1' and name like 'Alc_%')



what i am trying to do is to get count of the names, having distinct letters in left 9 positions and some conditions too. How to do it?

View 5 Replies View Related

Count Of Distinct Rows Of Only Selected Columns?

Aug 9, 2012

I am trying to just get a count of the number of distinct rows are in a table --- not looking at the entire row of fields, but only selecting a few.

i don't want to see the distinct rows, i just want a count of how many are in the table.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved