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 CarterPosted Apr 6, 2025, 2:50 AM
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:
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!