CODEWITHSUNDEEP
sqlsql-servert-sql
shalin gajjar
shalin gajjar

Reputation: 684

how to use distinct and group with sql statement

i have created one query that is for parent summary list. here i just want to get records multiple time because mutiple entries to another table : here it is :

ALTER PROCEDURE [dbo].[GetParentList](@searchtext varchar(200) = null)
AS
BEGIN
    SET NOCOUNT ON;
    select distinct * from
    (select *, right('00000' + CAST([PK_PID] AS varchar(5)) ,5) as ID,(select count(*) from parentstudentassign ps where ps.ParentID = p.PK_PID) as Kids
    from parents p
    left join parentstudentassign ps on ps.ParentID = p.PK_PID

    where (@searchtext is null  or p.[PK_PID] like '%'+@searchtext+'%'
                            or p.[ParentName] like '%'+@searchtext+'%'
                            or p.[Gender] like '%'+@searchtext+'%'
                            or p.[Ocuption] like '%'+@searchtext+'%'
                            or p.[Address] like '%'+@searchtext+'%'
                            or p.[MobileNo] like '%'+@searchtext+'%'
                            or p.[EmailID] like '%'+@searchtext+'%') 
    order by PK_PID asc) as test1
    group by Kids
END

here i dont't want entries as multiple time:

enter image description here

and this is error occurs :

enter image description here

whats' going wrong here. please help me to out.

Upvotes: 0

Views: 57

Answers (1)

gotqn
gotqn

Reputation: 43636

Try this:

ALTER PROCEDURE [dbo].[GetParentList](@searchtext varchar(200) = null)
AS
BEGIN
    SET NOCOUNT ON;
    select distinct * 
    from
    (
        select *, right('00000' + CAST([PK_PID] AS varchar(5)) ,5) as ID,(select count(*) from parentstudentassign ps where ps.ParentID = p.PK_PID) as Kids
        from parents p
        left join parentstudentassign ps on ps.ParentID = p.PK_PID

        where (@searchtext is null  or p.[PK_PID] like '%'+@searchtext+'%'
                                or p.[ParentName] like '%'+@searchtext+'%'
                                or p.[Gender] like '%'+@searchtext+'%'
                                or p.[Ocuption] like '%'+@searchtext+'%'
                                or p.[Address] like '%'+@searchtext+'%'
                                or p.[MobileNo] like '%'+@searchtext+'%'
                                or p.[EmailID] like '%'+@searchtext+'%') 
    ) as test1
    order by PK_PID asc
END

Upvotes: 1

Related Questions