I have this query :
SELECT kailiv, count(*) as PALETTES from FGE50NEUV1.gesupe
where etasup='30' and cumcol>0 and kailiv>=1
and kailiv<=66
group by kailiv
order by kailiv
This returns things like :
[
{ KAILIV: 1, PALETTES: 15 },
{ KAILIV: 2, PALETTES: 7 },
{ KAILIV: 3, PALETTES: 2 },
{ KAILIV: 6, PALETTES: 4 },
{ KAILIV: 7, PALETTES: 1 },
{ KAILIV: 13, PALETTES: 10 },
{ KAILIV: 15, PALETTES: 3 },
{ KAILIV: 20, PALETTES: 8 },
{ KAILIV: 26, PALETTES: 2 },
{ KAILIV: 27, PALETTES: 1 },
{ KAILIV: 29, PALETTES: 10 },
{ KAILIV: 30, PALETTES: 10 },
{ KAILIV: 31, PALETTES: 4 },
{ KAILIV: 32, PALETTES: 10 },
{ KAILIV: 62, PALETTES: 7 },
{ KAILIV: 63, PALETTES: 6 },
{ KAILIV: 64, PALETTES: 4 }
]
And I would like to have a row for each "kailiv" between 1 and 66, with a 0 if it's empty
Is it easier to make it after fetching the data through JS, or directly in SQL, and how can I do this ?
EDIT : When I try this after the query :
for(let i=1; i<=66; i++) {
let quai = travees[i].KAILIV
if (quai != i) {
travees.slice(i-1,0,{KAILIV : i, PALETTES: 0})
}
}
I've got an error 'Cannot read properties of undefined (reading 'KAILIV')
It's easy enough to do in javascript, the general idea is turn your array into a lookup keyed on KAILIV and then just loop from 1 to 66 pulling either the value or 0
const input = [
{ KAILIV: 1, PALETTES: 15 },
{ KAILIV: 2, PALETTES: 7 },
{ KAILIV: 3, PALETTES: 2 },
{ KAILIV: 6, PALETTES: 4 },
{ KAILIV: 7, PALETTES: 1 },
{ KAILIV: 13, PALETTES: 10 },
{ KAILIV: 15, PALETTES: 3 },
{ KAILIV: 20, PALETTES: 8 },
{ KAILIV: 26, PALETTES: 2 },
{ KAILIV: 27, PALETTES: 1 },
{ KAILIV: 29, PALETTES: 10 },
{ KAILIV: 30, PALETTES: 10 },
{ KAILIV: 31, PALETTES: 4 },
{ KAILIV: 32, PALETTES: 10 },
{ KAILIV: 62, PALETTES: 7 },
{ KAILIV: 63, PALETTES: 6 },
{ KAILIV: 64, PALETTES: 4 }
]
// make a map of the data
const indexed = Object.fromEntries(input.map(x => [x.KAILIV,x.PALETTES]));
// function to generate a range of numbers (1-66 in our case)
function range(size, startAt = 0) {
return [...Array(size).keys()].map(i => i + startAt);
}
// generate a new set of data
const newData = range(66,1).map(k=> ({
KAILIV:k,
PALETTES: indexed[k] ?? 0
}))
console.log(newData);