CODEWITHSUNDEEP
sqlsql-servergroup-by
Enmy Pérez Moncada
Enmy Pérez Moncada

Reputation: 93

GROUP BY error in SQL Server

When I delete Group By clause it works. The main problem is that the query gets repeated rows, so I added Group By to clear the results.

SELECT TOP 5 
    O.ItemCode, O.ItemName
FROM OITM O 
    JOIN OITW OI ON O.ItemCode = OI.ItemCode 
WHERE O.ItemName LIKE '%a%' 
GROUP BY O.ItemCode 
ORDER BY PATINDEX ('a%', O.ItemName) DESC, 
    LEN(O.ItemName) ASC

The error I get is just

[unixODBC][FreeTDS][SQL Server]The cursor was not declared.

But deleating the line GROUP BY O.ItemCode fix it

EDIT: Adding all the rows works in last query. Thanks

Now this is the complete query

SELECT TOP 5 
    O.ItemCode, O.CodeBars, O.ItemName, CONVERT(INT, OI.OnHand) AS Stock
FROM OITM O 
    JOIN OITW OI ON O.ItemCode = OI.ItemCode WHERE O.ItemName LIKE '%a%' 
GROUP BY O.ItemCode, O.CodeBars, O.ItemName, Stock
ORDER BY PATINDEX ('a%', O.ItemName) DESC, 
    LEN(O.ItemName) ASC, 
    PATINDEX ('%a%', O.ItemName) DESC, 
    PATINDEX ('%a', O.ItemName) DESC

Error

[unixODBC][FreeTDS][SQL Server]Invalid column name 'Stock'.

Upvotes: 0

Views: 91

Answers (2)

xQbert
xQbert

Reputation: 35353

IMO: GROUP BY is generally for use with aggregate functions (Sum, Count, Max, Min, Avg)

DISTINCT is to eliminate duplicates when selecting non key values

I think you need to sum your stock as OI could have multiple onHand values for each item.

 SELECT TOP 5 O.ItemCode
            , O.CodeBars
            , O.ItemName, 
            , sum(CONVERT(INT, OI.OnHand)) AS Stock
FROM OITM O 
INNER JOIN OITW OI 
   ON O.ItemCode = OI.ItemCode 
WHERE O.ItemName LIKE '%a%' --Very slow can't use index
GROUP BY O.ItemCode
       , O.CodeBars
       , O.ItemName
ORDER BY PATINDEX ('a%', O.ItemName) DESC
       , LEN(O.ItemName) ASC
       , PATINDEX ('%a%', O.ItemName) DESC
       , PATINDEX ('%a', O.ItemName) DESC

Or you need to add your convert to a group by but then you have multiple itemcode, codebars and itemname's possible which I think you were trying to avoid and since your order by doesn't take into account the onhand values, I think a sum is what you're after (or a min/max/avg)....

The group by doesn't have knowledge of the alias at run time. so you have to enter the formula. Only the order by would have knowledge of the alias.

 SELECT TOP 5 O.ItemCode
            , O.CodeBars
            , O.ItemName, 
            , CONVERT(INT, OI.OnHand) AS Stock
FROM OITM O 
INNER JOIN OITW OI 
   ON O.ItemCode = OI.ItemCode 
WHERE O.ItemName LIKE '%a%'  --very slow can't use index
GROUP BY O.ItemCode
       , O.CodeBars
       , O.ItemName
       , CONVERT(INT, OI.OnHand) --alias not available must use formula
ORDER BY PATINDEX ('a%', O.ItemName) DESC
       , LEN(O.ItemName) ASC
       , PATINDEX ('%a%', O.ItemName) DESC
       , PATINDEX ('%a', O.ItemName) DESC

Upvotes: 2

LONG
LONG

Reputation: 4620

SELECT TOP 5 
    O.ItemCode, O.ItemName
FROM OITM O 
    JOIN OITW OI ON O.ItemCode = OI.ItemCode 
WHERE O.ItemName LIKE '%a%' 
GROUP BY O.ItemCode,O.ItemName
ORDER BY PATINDEX ('a%', O.ItemName) DESC, 
    LEN(O.ItemName) ASC

Upvotes: 0

Related Questions