November 25, 2014 at 4:37 am
CREATE TABLE #A
(
NAME VARCHAR (10),
stockValue INT,
cash INT,
)
INSERT INTO #A ( NAME, stockValue, cash ) VALUES ( 'Alice', 20, 50)
INSERT INTO #A ( NAME, stockValue, cash ) VALUES ( 'Bob', 30, 65)
INSERT INTO #A ( NAME, stockValue, cash ) VALUES ( 'Foo', 50, 35)
INSERT INTO #A ( NAME, stockValue, cash ) VALUES ( 'Dan', 25, 30)
INSERT INTO #A ( NAME, stockValue, cash ) VALUES ( 'Jane', 29, 30)
I would like a select statement which will group Foo with Alice and Dan with Bob
So the result should be something like
Name StockValue Cash
---------------------------------
Alice 70 85
Bob 55 95
Jane 29 30
Thanks for the help
November 25, 2014 at 5:13 am
May be like this..there can be another way to do this
SELECT CASE WHEN NAME IN ('Foo','Alice') THEN 'Alice'
WHEN NAME IN ('Dan','Bob') THEN 'Bob'
ELSE NAME
END NAME,
SUM(stockValue) stockValue ,
SUM(cash) cash
FROM #A
GROUP BY CASE WHEN NAME IN ('Foo','Alice') THEN 'Alice'
WHEN NAME IN ('Dan','Bob') THEN 'Bob'
ELSE NAME
END
November 25, 2014 at 6:14 am
Thanks for the reply...it work and I understand it..
You say there can be other ways..can u hint like what ways?
November 25, 2014 at 7:06 am
You could create a table that defines how you want your results grouped and then join it in. Here's an example:
CREATE TABLE #Groups (
Name varchar(10),
GroupName Varchar(10));
insert into #Groups(Name, GroupName)
values('Foo', 'Alice'),
('Alice', 'Alice'),
('Dan', 'Bob'),
('Bob', 'Bob');
SELECT ISNULL(g.GroupName, a.NAME), SUM(a.stockValue), SUM(a.cash)
FROM #A a
LEFT OUTER JOIN #Groups g on g.Name = A.NAME
GROUP BY ISNULL(g.GroupName, a.NAME)
ORDER BY ISNULL(g.GroupName, a.NAME);
From the table name, I presume that this isn't an actual example, but rather an isolated example to look at approaches to the problem. If you're going to put this type of thing into production, I would normalize your #A table and not join the #Groups by varchar(10), but by a good key.
November 25, 2014 at 7:35 am
nice! looks neat
Yeah you are this right I have to put this in production where there are more than 50 rows. Would it be nice to have a temp table with groups???
November 25, 2014 at 8:04 am
If your data is in a real table, why not put your grouping in a real table?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply