'count how many time an item has been used, and reset that number

need some help please... my problem is, there are some items lets say a1,a2,a,3 - b1,b2,b3 - c1,c2,c3 which after certain uses (4) need to be cleansed to be used again. i want to keep some data about this process so i made the following table in SQL

CREATE table items
(
  itemName varchar(10),
  concept varchar(10)
)

itemName is self explanatory: a1,a2,a3 etc. concept is either "Use" or lets say.. "Cleanse"

data example

INSERT INTO items (itemName, concept) 
values 
    ('a1','Use'),
    ('a2','Use'),
    ('c1','Use'),
    ('b3','Use'),
    ('c2','Use'),
    ('a1','Use'),
    ('a1','Use'),
    ('a1','Use'),
    ('c2','Use'),
    ('a1','Cleanse')
select COUNT(*) as count,itemname, concept from items group by concept, itemName

enter image description here

so every 4 insert of item "a1" with concept "Use" the next one has to be "Cleanse" for now, i have been able to keep track of the process but, i've noticed some items being used more or less times than 4.

now, the problem is... i have no idea how make a counter limited to 4 and how to reset it after.. perhaps, it needs a table for only Use and another for Cleanse ??. i though with a simple count(*) would be enough... i was so wrong.

perhaps i need a validation somewhere else ._. ugh im lost

oh and btw im using tkinter gui to enter this data

so.. please some enlightenment

i imagined with a count(*) query would work



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source