2

So far, I am using this Query:

SELECT "FailCode", 
count (*) FILTER (WHERE "FailCode" =10 or "FailCode"=14 or "FailCode"=18 or "FailCode"=23 or "FailCode"=27 or "FailCode"=35 or "FailCode"=40) as "Ch1",
count (*) FILTER (WHERE "FailCode" =11 or "FailCode"=15 or "FailCode"=19 or "FailCode"=24 or "FailCode"=28 or "FailCode"=36 or "FailCode"=41) as "Ch2",
count (*) FILTER (WHERE "FailCode" =12 or "FailCode"=16 or "FailCode"=20 or "FailCode"=25 or "FailCode"=29 or "FailCode"=37 or "FailCode"=42) as "Ch3",
count (*) FILTER (WHERE "FailCode" =13 or "FailCode"=17 or "FailCode"=21 or "FailCode"=26 or "FailCode"=30 or "FailCode"=38 or "FailCode"=43) as "Ch4"
FROM public.minical01
group by 1
order BY 1;

Which gives me these results enter image description here

But what I am seeking to generate a new column ("new_column") with 7 rows (as a new integer for every new row 1,2..6,7) from a range of integers from the first column "FailCode" without displaying the "FailCode" column... and instead I want to display only the "new_column" and the "Ch1", "Ch2", "Ch3", "Ch4" columns.

Every row (as integer) for the "new_column" should represent the following ranges:

  1. (WHERE "FailCode" >= 10 and "FailCode" <= 13) as 1
  2. (WHERE "FailCode" >= 14 and "FailCode" <= 17) as 2
  3. (WHERE "FailCode" >= 18 and "FailCode" <= 21) as 3
  4. (WHERE "FailCode" >= 23 and "FailCode" <= 26) as 4
  5. (WHERE "FailCode" >= 27 and "FailCode" <= 30) as 5
  6. (WHERE "FailCode" >= 35 and "FailCode" <= 38) as 6
  7. (WHERE "FailCode" >= 40 and "FailCode" <= 43) as 7

Any hints or ideas would be greatly appreciated. Thank you!!

Improve this question

2 Answers 2

Reset to default
1

I think you're looking for the CASE expression. Because of the aggregation the CASE switch has to run in an outer query, but that lets us rearrange columns as needed:

Demonstration fiddle

select
  "Ch1",
  "Ch2",
  "Ch3",
  "Ch4",
  CASE WHEN "FailCode" >= 10 and "FailCode" <= 13 THEN 1
       WHEN "FailCode" >= 14 and "FailCode" <= 17 THEN 2
       WHEN "FailCode" >= 18 and "FailCode" <= 21 THEN 3
       WHEN "FailCode" >= 23 and "FailCode" <= 26 THEN 4
       WHEN "FailCode" >= 27 and "FailCode" <= 30 THEN 5
       WHEN "FailCode" >= 35 and "FailCode" <= 38 THEN 6
       WHEN "FailCode" >= 40 and "FailCode" <= 43 THEN 7
   END AS new_column
from (
SELECT "FailCode", 
count (*) FILTER (WHERE "FailCode" =10 or "FailCode"=14 or "FailCode"=18 or "FailCode"=23 or "FailCode"=27 or "FailCode"=35 or "FailCode"=40) as "Ch1",
count (*) FILTER (WHERE "FailCode" =11 or "FailCode"=15 or "FailCode"=19 or "FailCode"=24 or "FailCode"=28 or "FailCode"=36 or "FailCode"=41) as "Ch2",
count (*) FILTER (WHERE "FailCode" =12 or "FailCode"=16 or "FailCode"=20 or "FailCode"=25 or "FailCode"=29 or "FailCode"=37 or "FailCode"=42) as "Ch3",
count (*) FILTER (WHERE "FailCode" =13 or "FailCode"=17 or "FailCode"=21 or "FailCode"=26 or "FailCode"=30 or "FailCode"=38 or "FailCode"=43) as "Ch4"
FROM public.minical01
group by 1
order BY 1
) t

Result table (the count results are not accurate, but will demonstrate how the new column gets added):

Ch1 Ch2 Ch3 Ch4 new_column
1 0 0 0 1
0 1 0 0 1
0 0 1 0 2
0 1 0 0 3
1 0 0 0 4
0 1 0 0 4
0 0 1 0 4
0 0 0 1 4
1 0 0 0 5
0 1 0 0 5
0 0 1 0 5
0 0 0 1 5

Update

To aggregate the data as you've requested, sum the Ch columns and GROUP BY our new column:

select
  sum("Ch1") AS "Ch1",
  sum("Ch2") AS "Ch2",
  sum("Ch3") AS "Ch3",
  sum("Ch4") AS "Ch4",
  CASE WHEN "FailCode" >= 10 and "FailCode" <= 13 THEN 1
       WHEN "FailCode" >= 14 and "FailCode" <= 17 THEN 2
       WHEN "FailCode" >= 18 and "FailCode" <= 21 THEN 3
       WHEN "FailCode" >= 23 and "FailCode" <= 26 THEN 4
       WHEN "FailCode" >= 27 and "FailCode" <= 30 THEN 5
       WHEN "FailCode" >= 35 and "FailCode" <= 38 THEN 6
       WHEN "FailCode" >= 40 and "FailCode" <= 43 THEN 7
   END AS new_column
from (
SELECT "FailCode", 
count (*) FILTER (WHERE "FailCode" =10 or "FailCode"=14 or "FailCode"=18 or "FailCode"=23 or "FailCode"=27 or "FailCode"=35 or "FailCode"=40) as "Ch1",
count (*) FILTER (WHERE "FailCode" =11 or "FailCode"=15 or "FailCode"=19 or "FailCode"=24 or "FailCode"=28 or "FailCode"=36 or "FailCode"=41) as "Ch2",
count (*) FILTER (WHERE "FailCode" =12 or "FailCode"=16 or "FailCode"=20 or "FailCode"=25 or "FailCode"=29 or "FailCode"=37 or "FailCode"=42) as "Ch3",
count (*) FILTER (WHERE "FailCode" =13 or "FailCode"=17 or "FailCode"=21 or "FailCode"=26 or "FailCode"=30 or "FailCode"=38 or "FailCode"=43) as "Ch4"
FROM minical01
group by 1
) t
where "FailCode" between 10 and 43
group by 5
order by 5
Ch1 Ch2 Ch3 Ch4 new_column
1 1 0 0 1
0 0 1 0 2
0 1 0 0 3
1 1 1 1 4
1 1 1 1 5

fiddle

Improve this answer
14
  • Thanks, @dwhitemv. It's so close to what I am looking for. However, I get a null value for one of the new_column rows, so how can I get rid of this row with a null value? This is what I have so far: i.stack.imgur.com/RIi6l.png) Also, based on these new results, this is I am trying to achieve, a table that looks which adds what I have on each channel for every item number: i.stack.imgur.com/agxBR.png In short, the results I am looking for should look like this i.stack.imgur.com/JWULD.png
    – Lalo Quera
    Dec 4, 2022 at 2:09
  • Please edit your question and add this new information. NULL happens if the FailCode is out of range; what value do you want displayed then?
    – dwhitemv
    Dec 4, 2022 at 15:31
  • To achieve the new desired result you just need to SUM each channel column and GROUP BY the new column.
    – dwhitemv
    Dec 4, 2022 at 16:00
  • I have updated the answer to incorporate the new information.
    – dwhitemv
    Dec 5, 2022 at 5:09
  • Thank you very much for your great help. One more question. How do you get rid of the Null character (which creates its row) from the "new_column" ?
    – Lalo Quera
    Dec 5, 2022 at 18:53
0

This what worked for me so far:

select
  sum("Ch1") AS "Ch1",
  sum("Ch2") AS "Ch2",
  sum("Ch3") AS "Ch3",
  sum("Ch4") AS "Ch4",
  CASE WHEN "FailCode" >= 10 and "FailCode" <= 13 THEN 1
       WHEN "FailCode" >= 14 and "FailCode" <= 17 THEN 2
       WHEN "FailCode" >= 18 and "FailCode" <= 21 THEN 3
       WHEN "FailCode" >= 23 and "FailCode" <= 26 THEN 4
       WHEN "FailCode" >= 27 and "FailCode" <= 30 THEN 5
       WHEN "FailCode" >= 35 and "FailCode" <= 38 THEN 6
       WHEN "FailCode" >= 40 and "FailCode" <= 43 THEN 7
       ELSE 7 /*all other values, including null*/
  END AS new_column
from (
SELECT "FailCode",
count (*) FILTER (WHERE "FailCode" =10 or "FailCode"=14 or "FailCode"=18 or "FailCode"=23 or "FailCode"=27 or "FailCode"=35 or "FailCode"=40) as "Ch1",
count (*) FILTER (WHERE "FailCode" =11 or "FailCode"=15 or "FailCode"=19 or "FailCode"=24 or "FailCode"=28 or "FailCode"=36 or "FailCode"=41) as "Ch2",
count (*) FILTER (WHERE "FailCode" =12 or "FailCode"=16 or "FailCode"=20 or "FailCode"=25 or "FailCode"=29 or "FailCode"=37 or "FailCode"=42) as "Ch3",
count (*) FILTER (WHERE "FailCode" =13 or "FailCode"=17 or "FailCode"=21 or "FailCode"=26 or "FailCode"=30 or "FailCode"=38 or "FailCode"=43) as "Ch4"
FROM minical01
where ("FailCode" between 10 and 43) and not ("FailCode" between 31 and 34) and t_stamp >= '2022-12-8 6:00:00.000' AND t_stamp <= '2022-12-8 12:00:00.000'    
group by 1
order by 1 
) t
where t is not null 
group by 5
order by 5
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.