Solved

SQL Query Assistance (Disinct Count)

Posted on 2014-12-30
25
126 Views
Last Modified: 2015-01-20
Hello, I am looking for assistance creating a query.  I need to count the number of distinct records based on the name of the record and rated by category.  See sample below.

SELECT DISTINCT COUNT(*) AS Count, [Category]
FROM [table name]
WHERE NAME LIKE [Name]
GROUP BY [Category]

I want to list the following columns
Count| Name| Category

Thank you
0
Comment
Question by:blackcatkempo
  • 12
  • 12
25 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40524313
It might help if you give some sample data and sample expected output,
0
 

Author Comment

by:blackcatkempo
ID: 40524342
Attached is sample data.  I need to sort through thousands of records similar.  Key columns Category and Name.  I want a count of a distinct name based on category.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40524402
Nothing attached.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40524421
COUNT can use DISTINCT inside it, which is really useful:

SELECT COUNT(DISTINCT Name) AS Count_Distinct_Names, [Category]
FROM [table name]
WHERE NAME LIKE [Name] --??
GROUP BY [Category]
0
 

Author Comment

by:blackcatkempo
ID: 40524638
0
 

Author Comment

by:blackcatkempo
ID: 40524813
I think I need to clarify.  Using the sample-data.csv.  I would like to report the total number of distinct records (name) that meet a specific criteria (category).  

SELECT DISTINCT COUNT(*) AS Count, Risk
FROM [tablename]
WHERE NAME LIKE 'Microsoft XML Parser%'
GROUP BY Category
ORDER BY Category ASC

Results in (columns in pipes) ---- I want another column with the name, which is related/assigned a category.
Count|Category
32| High

Need to result with following columns.

Count of Distinct Name | Name | Category

Please let me know if I need to provide further detail.  Thank you.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40525030
Without a sample data set leading to a sample result, I cannot help you.

Your attached sample data does not lead to a result of 32, High, and therefore I would still be guessing as to what you want.
0
 

Author Comment

by:blackcatkempo
ID: 40525441
Attached is a sanitized version for sample data.  Using this file, I would like to see the following columns in a query displaying the number of distinct occurrences of the name record.

Count of Distinct Name | Name | Risk

Count of distinct name - the number of instances of the name
Name - the name of the risk
Risk - the risk level

Does this help?
0
 

Author Comment

by:blackcatkempo
ID: 40525442
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40525532
Select [Name], Count(Distinct [Name]) as CountOfDistinctName, Risk
From [tablename]
Group by [Name], Risk

From what you have described, the only thing wrong with your previous example was the placement of the word "Distinct".
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40525535
Note that it is very similar to Scott's solution.
0
 

Author Comment

by:blackcatkempo
ID: 40525624
Thank you for the query. I would like to see the number of distinct occurrences (risks) displayed for each name .  The script your provided only displays a "1" for each row (name).  I apologize if i am not explaining properly.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40525939
Then

Select [Name], Count(Distinct [Risk]) as CountOfDistinctName, Risk
From [tablename]
Group by [Name], Risk
0
 

Author Comment

by:blackcatkempo
ID: 40525960
Sorry that query provided the same result - only displays a "1".  I have confirmed there are more then one instance per name using a separate individual query.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40525975
Select [Name],
    (select Count(Distinct [Risk])
    From [tablename] as U
    Where T.[Name] = U.[Name]) as CountOfDistinctName,
Risk
From [tablename] as T
Group by [Name], Risk
0
 

Author Comment

by:blackcatkempo
ID: 40525995
Same result.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527653
Try this:

Select [Name], 
    (select Count([Risk]) 
    From [tablename] as U
    Where T.[Name] = U.[Name]) as CountOfDistinctName,
Risk
From [tablename] as T
Group by [Name], Risk

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40527654
This gives this result:

Adobe Reader Detection      1      None
Adobe Reader Enabled in Browser (Internet Explorer)      1      None
Antivirus Software Check      1      None
BIOS Version (WMI)      1      None
Citrix Online Plug-in Installed      1      None
Citrix Receiver / Online Plug-in Remote Code Execution (CTX134681)      1      High
Common Platform Enumeration (CPE)      4      None
Computer Manufacturer Information (WMI)      1      None
DCE Services Enumeration      4      None
0
 

Author Comment

by:blackcatkempo
ID: 40527673
Thanks Phillip, can I also use Count(Distinct Host)?  The code you provided works great, however the data table contains multiple risks for the same host.  I just want to count the single risk by host.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40527678
Yes, you can - note the difference in DCE Services Enumeration:

Adobe Reader Detection      1      None
Adobe Reader Enabled in Browser (Internet Explorer)      1      None
Antivirus Software Check      1      None
BIOS Version (WMI)      1      None
Citrix Online Plug-in Installed      1      None
Citrix Receiver / Online Plug-in Remote Code Execution (CTX134681)      1      High
Common Platform Enumeration (CPE)      4      None
Computer Manufacturer Information (WMI)      1      None
DCE Services Enumeration      1      None
0
 

Author Closing Comment

by:blackcatkempo
ID: 40544906
Thank you!
0
 

Author Comment

by:blackcatkempo
ID: 40559117
Hi Phillip,

One more follow up question regarding this query you crafted for me.  For some reason I can not use the WHERE IN clause for the column risk. I am specifically looking for the number of Critical, High, etc.  The results provide the number of all rows with a risk.  See query below. Thank you.

Select Distinct [Host], Risk,
    (select Count (Distinct [Name])
    From [tablename] as U
    Where T.[Host] = U.[Host]) as CountOfDistinctRisk
From [tablename] as T
WHERE Risk IN ('Critical','High')
Group by [Host], Name, Risk
ORDER by [CountofDistinctRisk] DESC
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40559373
Shouldn't it be inside the brackets?

Select Distinct [Host], Risk,
    (select Count (Distinct [Name])
    From [tablename] as U
    Where T.[Host] = U.[Host] AND Risk IN ('Critical','High')) as CountOfDistinctRisk
From [tablename] as T
Group by [Host], Name, Risk
ORDER by [CountofDistinctRisk] DESC

Open in new window

0
 

Author Comment

by:blackcatkempo
ID: 40559524
Having the AND statement inside of the sub query provides different results that are not accurate.  I actually do no know what it is reporting/resulting, essentially the countofdistinctrisk column produces a number that is not made up the total risks aggregate or something else.  We need the count of total number of critical, high, per hostname.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40559579
Sounds like this would work, if all you want is a filtered list:

Select [Name], 
    (select Count([Risk]) 
    From [tablename] as U
    Where T.[Name] = U.[Name]) as CountOfDistinctName,
Risk
From [tablename] as T
WHERE Risk IN ('Critical','High')
Group by [Name], Risk

Open in new window


I wouldn't understand why it wouldn't work.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question