CODEWITHSUNDEEP
sqlsql-serversql-server-2012
sam5808
sam5808

Reputation: 31

how to use group by function in the condition?

my inner query is working well but when i use group by function i am getting an error "Column 'b.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." i have used multiple tricks but none of them is working(like distinct)

select * from (
        SELECT  tsr.id,tblstckid,ts.articleNo,ts.code,size,ts.insertDate  FROM [tbl_setsRegister] tsr
APPLY dbo.Split1(tsr.tblstckid, ',') AS htvf 
        inner join tbl_stock ts on ts.id=htvf.Item where ts.id not in(60,62) and ts.articleNo='121010' and code='unknown-1')
group by b.tblstckid

what i want is to get distinct values from tblstckid with other columns also

sample data

id  tblstckid   articleNo   cCode   size    insertDate
21  55,56,58,   121010  unknown-1   34  2018-09-20 19:44:00.000
21  55,56,58,   121010  unknown-1   36  2018-09-20 19:44:00.000
21  55,56,58,   121010  unknown-1   38  2018-09-20 19:44:00.000
22  57,         121010  unknown-1   36  2018-09-20 19:44:00.000
23  59,61,63,   121010  unknown-1   34  2018-09-20 19:46:00.000
23  59,61,63,   121010  unknown-1   36  2018-09-20 19:46:00.000

i want get rid of duplicate tblstckid

Upvotes: 0

Views: 72

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112279

You must apply an aggregate function to all the columns not in the GROUP BY clause. A possible solution is:

SELECT
    id, tblstckid, articleNo, code,
    SUM(size) AS tot_size, MAX(ts.insertDate) AS lastInsertDate
FROM (
    SELECT tsr.id, tblstckid, ts.articleNo, ts.code, size, ts.insertDate
    FROM [tbl_setsRegister] tsr
    APPLY dbo.Split1(tsr.tblstckid, ',') AS htvf 
    INNER JOIN tbl_stock ts
        ON ts.id = htvf.Item
    WHERE ts.id NOT IN(60,62) AND ts.articleNo = '121010' AND code = 'unknown-1'
) b
GROUP BY id, tblstckid, articleNo, code

You must decide which aggregate function makes most sense. If you want to keep all values, you can also use STRING_AGG

 STRING_AGG (CAST(size AS varchar(10), ', ') AS sizes

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

You must put all the columns of the SELECT-list into GROUP BY except aggregation columns. As you select all but put only one column in GROUP BY, it throws an error

SELECT b.tblstckid
FROM (
    SELECT tsr.id, tblstckid, ts.articleNo, ts.code, size, ts.insertDate
    FROM [tbl_setsRegister] tsr
    APPLY dbo.Split1(tsr.tblstckid, ',') AS htvf 
    INNER JOIN tbl_stock ts
        ON ts.id = htvf.Item
    WHERE ts.id NOT IN(60,62) AND ts.articleNo = '121010' AND code = 'unknown-1'
) b
GROUP BY b.tblstckid

However, as you have not any aggregate function, but you used GROUP BY that is confusing

you can use row_number

SELECT * FROM
(
    SELECT tsr.id, tblstckid, ts.articleNo, ts.code, size, ts.insertDate,
        ROW_NUMBER() OVER(
            PARTITION BY tsr.id, tblstckid, ts.articleNo, ts.code, ts.insertDate
            ORDER BY size
        ) AS rn
    FROM [tbl_setsRegister] tsr
    APPLY dbo.Split1(tsr.tblstckid, ',') AS htvf 
    INNER JOIN tbl_stock ts
        ON ts.id = htvf.Item
    WHERE ts.id NOT IN(60,62) AND ts.articleNo = '121010' AND code = 'unknown-1'
) b
WHERE b.rn=1

Upvotes: 3

Related Questions