I have a table (MyTable) with 3 columns; Number, Line, Amount.
If Number is the same in more than 3 records, I want to return these records grouped by Number. Otherwise I want to all other records grouped by Number and Line.
This is what I got so far. It gives an error as Line needs to be in an aggregated function
select * (
select Number, (case when count(Line) > 3 then -1 else Line end) as Line2, sum(Amount)
from MyTable
group by Number, Line2
) as x
order by x.Number
MyTable looks something like this:
Number Line Amount
------------------------
1 1 100
1 1 100
1 2 200
1 2 200
2 1 150
2 1 150
3 1 300
3 2 350
I want the result look something like this:
Number Line2 Amount
------------------------
1 -1 600 <- More than 3 lines
2 1 300 <- Grouped by Line
3 1 300
3 2 350