Query Help Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help Count

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2014-08-08 : 14:22:43

CREATE TABLE [dbo].[Codes](
[CodeID] [int] IDENTITY(1,1) NOT NULL,
[Code1] [int] NULL,
[Code2] [varchar](10) NULL,
[Code3] [varchar](10) NULL,
CONSTRAINT [PK_Codes] PRIMARY KEY CLUSTERED
(
[CodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into Codes values(1,null,null)
insert into Codes values(1,null,null)
insert into Codes values(2,null,null)
insert into Codes values(3,'a','i')
insert into Codes values(3,'a','i')
insert into Codes values(3,'a','ii')
insert into Codes values(3,'b','i')
insert into Codes values(3,'b','ii')
insert into Codes values(3,'b','ii')
insert into Codes values(3,'c','')

Expected Results:
Code codeCount
1 2
2 1
3ai 2
3aii 1
3bi 1
3bii 2
3c 1

I need help figuring this out. I can do a count if there's only a code1, but I can't figure out how to combine when there are code2 & code3.

Can anyone help me figure this out?

Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-08-08 : 18:13:46

[code]select cast(Code1 as varchar(10)) + coalesce(Code2, '') + coalesce(Code3, '') as Code, count(*) as CodeCount
from dbo.Codes
group by Code1, Code2, Code3[/code]

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

ann
Posting Yak Master

220 Posts

Posted - 2014-08-08 : 20:19:24

wow - you made that look so easy.

Thanks - worked great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources