I have a table :
CREATE TABLE ask (
item TEXT ,
value INTEGRER NOT NULL
);
INSERT INTO ask (item,value) VALUES
("A", 1),
("A", 4),
("A", 3),
("B", 0),
("B", 1),
("C", 2),
("C", 4);
item | value |
---|---|
A | 1 |
A | 4 |
A | 3 |
B | 0 |
B | 1 |
C | 2 |
C | 4 |
I want two additional columns with max and min values per item:
item | value | min_val | max_val |
---|---|---|---|
A | 1 | 1 | 4 |
A | 4 | 1 | 4 |
A | 3 | 1 | 4 |
B | 0 | 0 | 1 |
B | 1 | 0 | 1 |
C | 2 | 2 | 4 |
C | 4 | 2 | 4 |
How can I get that in SQLite? This doesn't work:
SELECT *, max(value) AS max_val, min(val) AS min_val FROM ask;
CodePudding user response:
Use window functions instead of aggregate functions:
SELECT *,
MIN(value) OVER (PARTITION BY item) min_val,
MAX(value) OVER (PARTITION BY item) max_val
FROM ask;
See the demo.
CodePudding user response:
You should not do this. Most likely, you should keep your table as is and create a view instead:
CREATE VIEW ask_minmax AS (
SELECT item,
max(value) AS maxval,
min(value) AS minval
FROM ask
GROUP BY item
);
CodePudding user response:
first of change text datatype to varchar in create table
CREATE TABLE ask (
item VARCHAR,
value Integer NOT NULL)
Insert Your Data
INSERT INTO ask (item,value)
VALUES
('A', 1),
('A', 4),
('A', 3),
('B', 0),
('B', 1),
('C', 2),
('C', 4);
Then after run my following query
With T1 As
(SELECT item,max(value) as max_val, min(value) as min_val FROM ask
GROUP bY item)
Select T2.item,T2.value,T1.min_val,T1.max_val From ask T2
join T1 On T1.item = T2.item
CodePudding user response:
You can create a temporary table with the max/mins and then join it to create the desired table/result.
I.e.
select item, max(value) maxval, min(value) minval
from ask group by item;
then
SELECT a.item, a.value, maxval, minval
from
ask a
join
(select item, max(value) maxval, min(value) minval from ask group by item) b
on a.item = b.item;