January 4, 2021 at 10:19 pm
i need to add a column to determine if ID and specified Type exist in the table (1 if exists, 0 if not)
Specific Type: B
ID Type Desired Column
1 A 1
1 B 1
2 A 0
3 A 0
3 A 0
4 B 1
4 B 1
4 A 1
5 A 1
5 A 1
5 A 1
5 B 1
Just to clarify:
ID=3 does not have a B type, so any rows with ID=3 should return 0.
ID=5 has one row that Type=5, so all rows with ID=5 should return 1.
Currently I have "SELECT *, sum(case when id||B in (id||type) then 1 else 0 end) as test", but that is only looking at the current row and returning 1 if THAT row is ID x and Type B. I need it to look at the whole column to see if ID x and Type B are ever listed.
In formulas I use COUNTIFS(A:A, A2, B:B, "B") or use a combined column C =A&B and COUNTIF (C:C,A2&"B")
Thanks
January 5, 2021 at 12:43 am
Please be aware that this is forum for SQL Server dbms. Your code is obviously for a different dbms. You might get a better response if you post on a forum for that dbms rather than here. At least let people know which dbms you are using so, if they happen to know it, they can respond.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 5, 2021 at 12:31 pm
It appears the OP is referencing Excel functions and column arrays. In this query the calculated NewColumn equals the DesiredColumn.
drop table if exists #tTable;
go
create table #tTable(
ID int not null,
[Type] char(1) not null,
DesiredColumn int not null);
insert #tTable(ID, [Type], DesiredColumn) values
(1, 'A', 1),
(1, 'B', 1),
(2, 'A', 0),
(3, 'A', 0),
(3, 'A', 0),
(4, 'B', 1),
(4, 'B', 1),
(4, 'A', 1),
(5, 'A', 1),
(5, 'A', 1),
(5, 'A', 1),
(5, 'B', 1);
select t.*, max(case when [Type]='B' then 1 else 0 end)
over (partition by ID order by (select null)) as NewColumn
from #tTable t;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 6, 2021 at 4:30 am
For over 30 years, SQL forms have required that you post DDL. Why do you feel you are exempt? Let me go ahead and do what you should have done if you had any manners at all.
>> I need to add a column to determine if <generic>id and specified <generic>_type exist in the table (1 if exists, 0 if not) <<
First of all, we do not randomly create columns in a table. You should be doing this in a VIEW or in a query. Back when we work with punchcards, we would do what you want because we had no choice. I hope you understand that by definition, and not as an option, a table must have a key.
CREATE TABLE Foobar
(foo_id CHAR(1) NOT NULL,
foo_type CHAR(1) NOT NULL
CHECK (foo_type IN ('A', 'B')),
PRIMARY KEY (foo_id, foo_type));
What you posted is not a table! You have duplicate rows! Where were you the first week of your RDBMS class? Here is how your table should have looked, if it actually was a table.
INSERT INTO Foobar
VALUES
(1, A), (1, B),
(2, A),
(3, A),
(4, B), (4, A),
(5, A), (5, B) ;
SELECT foo_id, foo_type
FROM Foobar
GROUP BY foo_id
HAVING MAX(foo_type = @search_type);
Just to clarify:
ID=3 does not have a B type, so any rows with ID=3 should return 0.
ID=5 has one row that Type=5, so all rows with ID=5 should return 1.
Did you even notice that "type = 5" is illegal? The rest of your posting has nothing whatsoever to do with SQL. You probably should not be on this forum.
>> Currently I have "SELECT *, sum(case when id||B in (id||type) then 1 else 0 end) as test", but that is only looking at the current row and returning 1 if THAT row is ID x and Type B. I need it to look at the whole column to see if ID x and Type B are ever listed. <<
The double pipe symbol is string concatenation in ANSI ISO standard SQL, and DB2. In particular. It is not now nor has it ever been part of the Sybase/Microsoft dialect of SQL. Perhaps more than that, what you posted with your case expression is just bad SQL. Concatenation of two atomic, unrelated, independent columns should not be used in a query. This is a violation of the principle of first normal form.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply