CODEWITHSUNDEEP
sqlsql-servert-sqlselect
levi
levi

Reputation: 3511

Select Result grouped by all fields

I want result grouped by all of the fields. I achieved it by the use of - WITH keyword. Is there any other way to do that? 

WITH    T AS ( SELECT   a.codeid AS VendorID ,
                                    a.hname ,
                                    a.hnamee ,
                                    CASE WHEN v.AudienceID = 0 THEN 1
                                         ELSE 0
                                    END AS HasAll
                           FROM     dbo.dtAny AS a
                                    LEFT JOIN VendorAudience AS v ON a.codeid = v.VendorID
                           WHERE    a.hrclvl = @level
                                    AND a.dcode = @dcode
                                    AND a.codeid = ISNULL(@VendorID, a.codeid)
                         )
    SELECT  VendorID ,
            hname ,
            hnamee ,
            HasAll
    FROM    T
    GROUP BY vendorid ,
            hname ,
            hnamee ,
            HasAll

I want something like this: 

  SELECT   a.codeid AS VendorID ,
                                    a.hname ,
                                    a.hnamee ,
                                    CASE WHEN v.AudienceID = 0 THEN 1
                                         ELSE 0
                                    END AS HasAll
                           FROM     dbo.dtAny AS a
                                    LEFT JOIN VendorAudience AS v ON a.codeid = v.VendorID
                           WHERE    a.hrclvl = @level
                                    AND a.dcode = @dcode
                                    AND a.codeid = ISNULL(@VendorID, a.codeid)
                                    GROUP BY VendorID ,a.hname ,a.hnamee ,HasAll

but there is error trying to group by HasAll.(I don't need to group by v.AudienceID)

Upvotes: 1

Views: 96

Answers (4)

Andrew McLintock
Andrew McLintock

Reputation: 142

Using DISTINCT is unnecessary if you are grouping by ALL the columns because the output will be unique anyway. I tend to agree with Miika above but there is not reason your original approach will not work.

Mac

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

That's what DISTINCT is used for:

WITH    T AS ( SELECT   a.codeid AS VendorID ,
                        a.hname ,
                        a.hnamee ,
                        CASE WHEN v.AudienceID = 0 THEN 1
                             ELSE 0
                        END AS HasAll
               FROM     dbo.dtAny AS a
                        LEFT JOIN VendorAudience AS v ON a.codeid = v.VendorID
               WHERE    a.hrclvl = @level
                        AND a.dcode = @dcode
                        AND a.codeid = ISNULL(@VendorID, a.codeid)
             )
SELECT DISTINCT *
FROM T

Upvotes: 0

Miika L.
Miika L.

Reputation: 3353

You can use it as a derived table.

SELECT VendorID ,a.hname ,a.hnamee ,HasAll
FROM (SELECT a.codeid AS VendorID ,
        a.hname ,
        a.hnamee ,
        CASE WHEN v.AudienceID = 0 THEN 1
            ELSE 0
        END AS HasAll
    FROM     dbo.dtAny AS a
        LEFT JOIN VendorAudience AS v ON a.codeid = v.VendorID
    WHERE    a.hrclvl = @level
        AND a.dcode = @dcode
        AND a.codeid = ISNULL(@VendorID, a.codeid)) [Table]
GROUP BY VendorID ,a.hname ,a.hnamee ,HasAll

Upvotes: 0

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

The query should be

SELECT   a.codeid AS VendorID ,
                                    a.hname ,
                                    a.hnamee ,
                                    CASE WHEN v.AudienceID = 0 THEN 1
                                         ELSE 0
                                    END AS HasAll
                           FROM     dbo.dtAny AS a
                                    LEFT JOIN VendorAudience AS v ON a.codeid = v.VendorID
                           WHERE    a.hrclvl = @level
                                    AND a.dcode = @dcode
                                    AND a.codeid = ISNULL(@VendorID, a.codeid)
                                    GROUP BY VendorID ,a.hname ,a.hnamee ,
                                    CASE WHEN   v.AudienceID = 0 THEN 1
                                         ELSE 0
                                    END

Upvotes: 1

Related Questions