Get Record Count From SQL table

If you have a sample table like this ...

create table table1(
	id int identity(1,1),
	SKU varchar (10),
	Title varchar(30),
	ImageName varchar(30)
)

insert into table1 values
('part1','apart','image11'),
('part2','apart','image21'),
('part1','apart','image12'),
('part1','apart','image13')

You can do any of the following to get the same results

select top 1 id, SKU, Title, ImageName, (select count(*) from table1 where SKU = 'part1')
from table1
where SKU = 'part1'

select TOP 1 A.id, A.SKU, Title, ImageName, B.countofsku
from (SELECT SKU, COUNT(*) AS countofsku FROM table1 GROUP BY SKU) b
INNER JOIN table1 A ON A.SKU = B.SKU
WHERE A.SKU = 'part1'

;WITH CTE AS
(
    SELECT SKU, COUNT(*) AS countofsku
    FROM table1 GROUP BY SKU
)
SELECT TOP 1 A.id, A.SKU, Title, ImageName, CTE.countofsku
FROM CTE
INNER JOIN table1 A ON A.SKU = CTE.SKU
WHERE A.SKU = 'part1'

(Edit - missed the WHERE clause on the last query on V1)

[EDIT - in response to further OP question in comments]:

Quote:

Thanks alot for the answer it worked but now i have one more complexity here what i want is i only want to get some records say only 15 records but in the same manner.

For example i want first 15 records and when i press load more next 15 and old 15 and so on... but with the above output

There is an excellent discussion here[^] on the various ways of paging results returned. Here is one way it can be done based on the last record of the previous call

USE [sandbox]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spPageResults]
@RowsPer INT,
@SKU varchar(10),
@PrevID INT OUTPUT
AS
BEGIN
	;WITH CTE AS
	(
		SELECT SKU, COUNT(*) AS countofsku
		FROM table1 GROUP BY SKU
	)
	SELECT TOP (@RowsPer) A.id, A.SKU, Title, ImageName, CTE.countofsku
	FROM CTE
	INNER JOIN table1 A ON A.SKU = CTE.SKU
	WHERE A.SKU = @SKU
	and A.id > @PrevID
	ORDER BY A.id

	SELECT TOP (@RowsPer) @PrevID = id       
	FROM table1
	WHERE SKU = @SKU
	and id > @PrevID
	ORDER BY id

END

GO

You can call it like this

DECLARE @RowsPer AS INT;
SET @RowsPer = 15;

DECLARE @PrevID INT
SET @PrevID = 0

EXECUTE dbo.spPageResults 5, 'part1', @PrevID OUTPUT
SELECT @PrevID
EXECUTE dbo.spPageResults 5, 'part1', @PrevID OUTPUT
SELECT @PrevID

@PrevID will show 7 on the first instance and 15 on the 2nd.

An alternative approach could be (less efficient):

-- Parameters to pass into the Stored Procedure
DECLARE @Page AS INT;
DECLARE @RowsPer AS INT;
SET @Page = 2;
SET @RowsPer = 15;


DECLARE @Start AS INT = ((@Page - 1) * @RowsPer) + 1
DECLARE @End AS INT = @Start + @RowsPer - 1

print @Start
print @End



;WITH CTE AS
(
    SELECT SKU, COUNT(*) AS countofsku
    FROM table1 GROUP BY SKU
),
CTE2 AS 
(
	SELECT id, SKU, Title, ImageName
	,ROW_NUMBER() OVER(ORDER BY ID) AS RowNum
	FROM table1 A
	WHERE SKU = 'part1'
)
SELECT CTE2.id, CTE2.SKU, CTE2.Title, CTE2.ImageName, CTE.countofsku, RowNum
FROM CTE2
INNER JOIN CTE ON CTE2.SKU = CTE.SKU
AND RowNum BETWEEN @Start AND @End