Count number or record from each Group in SQL Server

Last Reply one month ago By dharmendr

Posted one month ago

hi,

last time someone help me to fix the "Len" in the store procedure.  i assign the total of each record to the column call "number1".

now, i want to count each record number and assign to the new column call "number".  

here is my code and please help.

SELECT 
PROGRAMS.AutoNum, 
PROGRAMS.OrgCode, 
PROGRAMS.OPTitle, 
CASE WHEN LEN(PROGRAMS.OPPubDescrip) > 700 THEN 2 ELSE 1
END AS number1,
COUNT(number1) AS number,
PROGRAMS.OPMissionS
FROM ORGANIZATIONS 
	INNER JOIN PROGRAMS 
		ON ORGANIZATIONS.OrgCode = PROGRAMS.OrgCode
WHERE ORGANIZATIONS.Status = 'Approved'

thanks. 

You are viewing reply posted by: dharmendr one month ago.
Posted one month ago Modified on one month ago

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