SQL Query Assistance (Disinct Count)

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
blackcatkempoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It might help if you give some sample data and sample expected output,
0
blackcatkempoAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Nothing attached.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Scott PletcherSenior DBACommented:
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
blackcatkempoAuthor Commented:
0
blackcatkempoAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
blackcatkempoAuthor Commented:
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
blackcatkempoAuthor Commented:
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Note that it is very similar to Scott's solution.
0
blackcatkempoAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then

Select [Name], Count(Distinct [Risk]) as CountOfDistinctName, Risk
From [tablename]
Group by [Name], Risk
0
blackcatkempoAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
blackcatkempoAuthor Commented:
Same result.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
blackcatkempoAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blackcatkempoAuthor Commented:
Thank you!
0
blackcatkempoAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
blackcatkempoAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.