CODEWITHSUNDEEP
mysql
Mirieri Mogaka
Mirieri Mogaka

Reputation: 561

Creating a column to keep count

I have a table with 4 columns ID, animalid , calvdate and AnimalidLactNo i want to add an extra column called lactationNo that does a count when the date of an animalid changes i would give it a count number

The following is my table

CREATE TABLE `calvingdatecombined` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `animalid` varchar(80) DEFAULT NULL,
  `calvDate` varchar(15) DEFAULT NULL
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=latin1;

My expected output :

#ID, animalid,calvDate,LactationID

1,ANIM400,2015-10-14,1
2,ANIM400,2015-10-15,2
3,ANIM400,2016-10-14,3
4,ANIM403,2015-10-14,1
5,ANIM404,2015-10-14,1

Upvotes: 1

Views: 31

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

In MySQL version < 8.0, we can emulate Row number functionality using Session variables:

SELECT 
  dt.ID, 
  @row_num := IF(@aid <> dt.animalid, 1, @row_num + 1) AS LactationID, 
  @aid := dt.animalid AS animalid, 
  dt.calvDate 
FROM 
(
  SELECT
    ID, 
    animalid, 
    calvDate
  FROM calvingdatecombined 
  ORDER BY animalid, calvDate, ID
) AS dt 
CROSS JOIN (SELECT @row_num := 0, 
                   @aid := '') AS user_init_vars 
ORDER BY dt.ID

Upvotes: 2

Related Questions