CODEWITHSUNDEEP
sqldb2
knight944
knight944

Reputation: 21

Count to begin at 1

I want to value a column with a count. I am looking through the table to find the next count number. If a row does not exists I would like to begin the count with 1 rather than 0. Can someone assist me with my below query. If a row does exist then I would like to simply take the column 3 and add 1 to the next sequence number. 

SELECT
COl1, 
Col2,
CAST((MAX(col3) + 1) AS SMALLINT) AS col3
FROM table1
GROUP BY col1, col2

Upvotes: -1

Views: 155

Answers (2)

Esperento57
Esperento57

Reputation: 17472

If you want all combinaison with 1 if one combinaison dont exist try this:

with combinaison as (
select distinct f1.col1, f2.col2 
from table1 f1 cross join table1 f2
)
SELECT f1.col1, f1.col2, ifnull(MAX(f1.col3), 0) + 1 AS col3
FROM table1 f1
GROUP BY f1.col1, f1.col2
union all
select f2.col1, f2.col2, 1 AS col3
from combinaison f2 left outer join table1 f3 on (f2.col1, f2.col2)=(f3.col1, f3.col2)
where f3.col1 is null

Upvotes: 0

Philippe
Philippe

Reputation: 558

You are using a MAX() function that returns the highest value. If you want to count the number of occurences (rows), you need to use the COUNT() function.

Edit

More details would be needed, but in SQL Server you could try something like this: 

SELECT col1, col2, count(1) + 1 as col3
FROM table1
GROUP BY col1, col2

It would count the number of distinct pair of col1 and col2, then add one to it and display it as col3. From what I understand, you do not really need to use the current value of col3, since you are recalculating it.

Upvotes: 2

Related Questions