Count no of Records

Question

Hi

If value is 21000030 it should return 4 not 5 . In first record 21000030 exists 2 times

21000032 then it should return 3 not 4. I want to use Sql Query

USE [Live]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OCRD](
[CardCode] [nvarchar](15) NOT NULL,
[CardName] [nvarchar](100) NULL,
[Emp_1] [nvarchar](10) NULL,
[Emp_2] [nvarchar](10) NULL,
[Emp_3] [nvarchar](10) NULL,
CONSTRAINT [OCRD_PRIMARY] PRIMARY KEY CLUSTERED
(
[CardCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

INSERT [dbo].[OCRD] ([CardCode], [CardName], N'21000030', N'21000030', NULL)
INSERT [dbo].[OCRD] ([CardCode], [CardName], N'21000032', N'21000030', NULL)
INSERT [dbo].[OCRD] ([CardCode], [CardName], N'21000038', N'21000032', NULL)
INSERT [dbo].[OCRD] ([CardCode], [CardName], N'21000040', N'21000033', N'21000030')
INSERT [dbo].[OCRD] ([CardCode], [CardName], N'21000030', N'21000032', N'21000032')

Thanks

Sophia Carter · Answer

Based on the details provided, it seems like you are looking to count the occurrences of specific values within the `CardCode` column of the `OCRD` table. You are interested in a SQL query that can accurately count these occurrences. Here is an SQL query that you can use to achieve this: SELECT CardCode, COUNT(CardCode) AS Occurrences FROM OCRD GROUP BY CardCode ORDER BY Occurrences DESC When you run this query, it will return the distinct `CardCode` values along with the count of their occurrences in the `OCRD` table. For the specific examples you mentioned: - For the value '21000030', the query will return a count of 4 as it appears 4 times in the records. - For the value '21000032', the query will return a count of 3 as it appears 3 times in the records. You can execute this query in your SQL environment to get the desired count of occurrences for each `CardCode`. Let me know if you need further assistance or have any other questions!