Count number or record from each Group in SQL Server

Last Reply 2 months ago By dharmendr

Posted 3 months 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 2 months ago.
Posted 2 months ago Modified on 2 months 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