July 28, 2011 at 4:12 pm
Greetings….
I would like to enumerate every Microsoft Bulletin (MSFTID) and ULTIMATE_SUPER combination while counting the occurrences of each MSFTID in the vwMY_VULNS view.
Here is the query I have constructed so far. This enumerated the MSFTID / Utlimate_Parent pairs as desired. There is a one to many relationship between the MSFTID and the ULTIMATE_SUPER. This simply means that a single Microsoft Bulletin (MSFTID) can be superseded by more than one MSFTID (BulletinNumber/ULTIMATE_SUPER). BulletingNumber, MSFTID and ULTIMATE_SUPER all represent a MS BulletinNumber : MS##-###)
This is my starting query:
SELECT DISTINCT A.MSFTID,
b.ULTIMATE_SUPER, b.servicepackname FROM vwMY_VULNS A
LEFT JOIN MSSUPERMAP B ON A.MSFTID = B.bulletinnumber
WHERE MSFTID IS NOT NULL AND MSFTID LIKE '%MS%'
GROUP BY A.MSFTID, b.ULTIMATE_SUPER, b.servicepackname
ORDER BY MSFTID, DBO.SERVICEPACKNAME
This Query gives me output in the form:
MSFTID ULTIMATE_SUPER SERVICEPACKNAME
MS10-035MS10-035SPNAME1
MS10-035MS10-035SPNAME2
MS10-035MS10-050SPNAME3
MS10-035MS10-050SPNAME4
MS10-035MS10-050SPNAME5
MS10-035MS10-050SPNAME6
MS10-035MS10-065SPNAME7
MS10-035MS10-065SPNAME8
MS10-050MS10-050SPNAME1
MS10-050MS10-050SPNAME2
MS10-050MS10-073SPNAME3
MS10-050MS10-073SPNAME4
MS10-050MS10-073SPNAME5
MS10-050MS10-092SPNAME6
MS10-050MS10-092SPNAME7
MS10-050MS10-092SPNAME8
I would like to add a column between MSFTID and ULTIMATE_SUPER that lists the count of each MSFTID from the vwMY_VULNS view. The output would look like this:
MSFTID COUNTULTIMATE_SUPER SERVICEPACKNAME
MS10-03511231MS10-035SPNAME1
MS10-03511231MS10-035SPNAME2
MS10-03511231MS10-050SPNAME3
MS10-03511231MS10-050SPNAME4
MS10-03511231MS10-050SPNAME5
MS10-03511231MS10-050SPNAME6
MS10-03511231MS10-065SPNAME7
MS10-03511231MS10-065SPNAME8
MS10-0509853MS10-050SPNAME1
MS10-0509853MS10-050SPNAME2
MS10-0509853MS10-073SPNAME3
MS10-0509853MS10-073SPNAME4
MS10-0509853MS10-073SPNAME5
MS10-0509853MS10-092SPNAME6
MS10-0509853MS10-092SPNAME7
MS10-0509853MS10-092SPNAME8
I thought that adding the COUNT aggregate function for A.MSFT gave me variable results for each distinct MSFTID. I tried this:
SELECT DISTINCT A.MSFTID,
COUNT(A.MSFTID) AS 'COUNT',
b.ULTIMATE_SUPER, b.servicepackname, A.name FROM vwMY_VULNS A
LEFT JOIN MSSUPERMAP B ON A.MSFTID = B.bulletinnumber
WHERE MSFTID IS NOT NULL AND MSFTID LIKE '%MS%'
GROUP BY A.MSFTID, b.ULTIMATE_SUPER, b.servicepackname, A.name
ORDER BY MSFTID
[/CODE]
I ended up with variable results in the COUNT column.
What approach can I take to achieve the desired result?
July 29, 2011 at 1:28 pm
Your query will return the count of table a for each group. BTW, you really don't need a distinct when you also have a group by. The group by will ensure that each record is distinct by the nature of what grouping means. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 3, 2011 at 2:14 pm
The way I usually handle these sort of problems, is to split the query in to two parts.
In the first part, you can make a temp table to store the counts by MSFTID:
CREATE TABLE #MSFTCounts
(
MSFTID INT PRIMARY KEY,
MSFTCount INT,
[Name] VARCHAR(MAX)
)
INSERT INTO #MSFTCounts (MSFTID, MSFTCount, Name)
SELECT A.MSFTID, COUNT(*), A.[Name]
FROM vwMY_VULNS A
LEFT JOIN MSSUPERMAP B ON A.MSFTID = B.bulletinnumber
WHERE MSFTID IS NOT NULL AND MSFTID LIKE '%MS%'
GROUP BY A.MSFTID, A.[Name]
And then in the second part, you can join to the other table to get the remaining values:
SELECT
A.MSFTID,
A.MSFTCount AS 'COUNT',
b.ULTIMATE_SUPER,
b.servicepackname,
A.name
FROM #MSFTCounts A
LEFT JOIN MSSUPERMAP B ON A.MSFTID = B.bulletinnumber
GROUP BY A.MSFTID, A.MSFTCount, b.ULTIMATE_SUPER, b.servicepackname, A.name
ORDER BY MSFTID
note that I haven't tested the syntax, so I may have missed something, since I didn't have sample code built to work with.
August 4, 2011 at 3:48 am
Hello,
I agree with kramaswamy in the strategy adopted, splitting the query in two parts because there are two different criteria to group results.
To join these two parts I prefer the WITH clause rather than a temp table, my code for kramaswamy solution will be
WITH MSFTCounts AS (
SELECT A.MSFTID, COUNT(*), A.[Name]
FROM vwMY_VULNS A
LEFT JOIN MSSUPERMAP B ON A.MSFTID = B.bulletinnumber
WHERE MSFTID IS NOT NULL AND MSFTID LIKE '%MS%'
GROUP BY A.MSFTID, A.[Name]
)
SELECT
A.MSFTID,
A.MSFTCount AS 'COUNT',
b.ULTIMATE_SUPER,
b.servicepackname,
A.name
FROM MSFTCounts A
LEFT JOIN MSSUPERMAP B ON A.MSFTID = B.bulletinnumber
GROUP BY A.MSFTID, A.MSFTCount, b.ULTIMATE_SUPER, b.servicepackname, A.name
ORDER BY MSFTID
I tested the syntax the same way than kramaswamy 🙂
Regards,
Francesc
August 4, 2011 at 4:37 am
SELECT A.MSFTID,
SUM(COUNT(*)) OVER(PARTITION BY A.MSFTID) AS 'COUNT',
b.ULTIMATE_SUPER,
b.servicepackname,
A.name
FROM vwMY_VULNS A
LEFT JOIN MSSUPERMAP B
ON A.MSFTID = B.bulletinnumber
WHERE a.MSFTID LIKE '%MS%'
GROUP BY A.MSFTID, b.ULTIMATE_SUPER, b.servicepackname, A.name
ORDER BY MSFTID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply