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