June 14, 2011 at 11:12 pm
Hi All,
I want to display records with the count of each record based on the name .can anyone help me with this??
I have data some thing like :
name-------------code----------status
AAA---------------1----------------y
AAA---------------1----------------y
BBB---------------2----------------y
i want display the output as:
name-------------code----------status----- recordcount
AAA---------------1----------------y-------------2----
BBB---------------2----------------y-------------1----
June 15, 2011 at 12:18 am
GROUP BY 😀 look it up
Select columns , count(column) from tb
where something
group by columns
-- optional having clause to filter
June 15, 2011 at 1:14 am
select name, code, status, COUNT(*) "rowcount" from tablename group by Name,code,status
Ryan
//All our dreams can come true, if we have the courage to pursue them//
June 15, 2011 at 6:22 am
It depends on what you mean by "count of each record based on the name". Using a GROUP BY is going to give you a unique count on the combination of name, code and status. If you wanted a unique count by name only, you could do something like this (left GROUP BY in so you could see difference):
DECLARE @tbl TABLE (
name CHAR(3)
, code CHAR(1)
, [status] CHAR(1)
)
INSERT INTO @tbl (name, code, [status]) VALUES ('AAA','1','y'), ('AAA','1','y'), ('AAA','1','n'),('BBB','2','y')
SELECT name, code, [status], COUNT(*) FROM @tbl GROUP BY name, code, [status];
WITH tbl_CTE (name, code, [status], GroupCount)
AS (SELECT name, code, [status], COUNT(name) AS GroupCount FROM @tbl GROUP BY name, code, [status])
SELECTt1.name, t1.code, t1.[status], t2.GroupCount AS RecordCount
FROMtbl_CTE t1
INNER JOIN (SELECT name, SUM(GroupCount) as GroupCount FROM tbl_CTE GROUP BY name) t2
ON t1.name = t2.name
Make sense?
_____________________________________________________________________
- Nate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply