Hi phonghue,
Check with the below query.
SELECT AutoNum,OrgCode,OPTitle,OPPubDescrip,number1,COUNT(number1) AS number,OPMissionS
FROM (
SELECT PROGRAMS.AutoNum,
PROGRAMS.OrgCode,
PROGRAMS.OPTitle,
PROGRAMS.OPPubDescrip,
CASE WHEN LEN(PROGRAMS.OPPubDescrip) > 700 THEN 2 ELSE 1
END AS number1,
PROGRAMS.OPMissionS
FROM ORGANIZATIONS
INNER JOIN PROGRAMS ON ORGANIZATIONS.OrgCode = PROGRAMS.OrgCode
WHERE ORGANIZATIONS.Status = 'Approved'
AND PROGRAMS.OPStatus = 'Active') t
GROUP BY AutoNum,OrgCode,OPTitle,OPPubDescrip,number1,OPMissionS
ORDER BY OrgCode
Chack the sample query.
SQL
SELECT Name,number1,COUNT(number1) Number FROM (
SELECT EmployeeID,FirstName + ' ' + LastName Name,
CASE WHEN LEN(CONVERT(VARCHAR(MAX),Notes)) > 200 THEN 2 ELSE 1
END AS number1
FROM Employees)t
GROUP BY EmployeeID,Name,number1
ORDER BY EmployeeID
Output
| Name |
number1 |
Number |
| Nancy Davolio |
1 |
1 |
| Andrew Fuller |
2 |
1 |
| Janet Leverling |
2 |
1 |
| Margaret Peacock |
2 |
1 |
| Steven Buchanan |
2 |
1 |
| Michael Suyama |
2 |
1 |
| Robert King |
2 |
1 |
| Laura Callahan |
1 |
1 |
| Anne Dodsworth |
1 |
1 |
