April 26, 2012 at 2:27 am
[font="Courier New"]Hi all,
I'd would like to ask some help from you gurus here 🙂 I'm fairly new in this so please forgive me.
I have two tables:
--------------------------------
| ID | TAG | COLOR |
| 0001 | true | WHITE |
| 0002 | false | WHITE |
| 0003 | true | BLACK |
| 0004 | false | BLACK |
| 0005 | true | WHITE |
| 0006 | false | WHITE |
----------------------------------
---------------------
| ID | COND |
| 0001 | jam |
| 0002 | bread |
| 0003 | jam |
| 0004 | bread |
| 0005 | spoon |
| 0006 | spoon |
----------------------
What I want to do is create a VIEW wherein
foreach distinct COND in tbl_2, i would count the number of IDs where tbl_1.COLOR is WHITE
Expected result:
-------------------------------------------
| COND | NUMBER OF ROWS WITH WHITE TAG |
| jam | 1 |
| bread | 1 |
| spoon | 2 |
-------------------------------------------[/font]
April 26, 2012 at 2:44 am
--First, let's create your sample data
--First table
CREATE TABLE #yourTable1 (ID INT, TAG VARCHAR(5) CHECK (TAG IN ('true','false')), COLOR VARCHAR(10));
INSERT INTO #yourTable1
SELECT ID, TAG, COLOR
FROM (VALUES(1, 'true', 'WHITE'),
(2, 'false', 'WHITE'),
(3, 'true', 'BLACK'),
(4, 'false', 'BLACK'),
(5, 'true', 'WHITE'),
(6, 'false', 'WHITE'))a(ID, TAG, COLOR);
--Second table
CREATE TABLE #yourTable2 (ID INT, COND VARCHAR(10));
INSERT INTO #yourTable2
SELECT ID, COND
FROM (VALUES(1, 'jam'),
(2, 'bread'),
(3, 'jam'),
(4, 'bread'),
(5, 'spoon'),
(6, 'spoon'))a(ID, COND);
--Now, let's look at your question: -
/*
foreach distinct COND in tbl_2, i would count the number of IDs where tbl_1.COLOR is WHITE
Expected result:
-------------------------------------------
| COND | NUMBER OF ROWS WITH WHITE TAG |
| jam | 1 |
| bread | 1 |
| spoon | 2 |
-------------------------------------------
*/
--OK, that's not really "set" thinking. What you're doing is thinking about what you want to do
--to each ROW, instead you should be thinking about what you want to do to each COLOUMN.
--So, how would we do this in a SELECT statement?
SELECT a.COND, COUNT(b.ID) AS [NUMBER OF ROWS WITH WHITE TAG]
FROM #yourTable2 a
INNER JOIN (SELECT ID
FROM #yourTable1
WHERE COLOR = 'WHITE') b ON a.ID = b.ID
GROUP BY a.COND;
--OK, the above produces: -
/*
COND NUMBER OF ROWS WITH WHITE TAG
---------- -----------------------------
bread 1
jam 1
spoon 2
So, now we want a view!
CREATE VIEW yourView AS
SELECT a.COND, COUNT(b.ID) AS [NUMBER OF ROWS WITH WHITE TAG]
FROM #yourTable2 a
INNER JOIN (SELECT ID
FROM #yourTable1
WHERE COLOR = 'WHITE') b ON a.ID = b.ID
GROUP BY a.COND
Done! And with no nasty looping either, making a much faster piece of code.
*/
April 26, 2012 at 3:17 am
WOW!!! i'm just amazed on how you guys do this! Thank you so much!
That was fantastic!
Buuuut, I'm sorry I forgot to add this:
foreach distinct COND in tbl_2, i would count the number of IDs where tbl_1.COLOR is WHITE, then new column for number of blacks
Expected result:
-------------------------------------------
| COND | NUMBER OF ROWS WITH WHITE TAG | NUMBER OF ROWS WITH BLACK TAG
| jam | 1 | 1
| bread | 1 | 1
| spoon | 2 | 0
-------------------------------------------
I can't see any way just to add this in your answer.
I'm thinking a totally new approach? maybe?
April 26, 2012 at 3:23 am
brally123 (4/26/2012)
WOW!!! i'm just amazed on how you guys do this! Thank you so much!That was fantastic!
Buuuut, I'm sorry I forgot to add this:
foreach distinct COND in tbl_2, i would count the number of IDs where tbl_1.COLOR is WHITE, then new column for number of blacks
Expected result:
-------------------------------------------
| COND | NUMBER OF ROWS WITH WHITE TAG | NUMBER OF ROWS WITH BLACK TAG
| jam | 1 | 1
| bread | 1 | 1
| spoon | 2 | 0
-------------------------------------------
I can't see any way just to add this in your answer.
I'm thinking a totally new approach? maybe?
Again, you're still looking at the rows and not the columns.
SELECT a.COND, SUM(b.[WHITE]) AS [NUMBER OF ROWS WITH WHITE TAG],
SUM(b.[BLACK]) AS [NUMBER OF ROWS WITH BLACK TAG]
FROM #yourTable2 a
INNER JOIN (SELECT ID,
CASE WHEN COLOR = 'WHITE' THEN 1 ELSE 0 END AS [WHITE],
CASE WHEN COLOR = 'BLACK' THEN 1 ELSE 0 END AS [BLACK]
FROM #yourTable1) b ON a.ID = b.ID
GROUP BY a.COND;
April 26, 2012 at 4:10 am
brally123 (4/26/2012)
[font="Courier New"]Hi all,I'd would like to ask some help from you gurus here 🙂 I'm fairly new in this so please forgive me.
I have two tables:
--------------------------------
| ID | TAG | COLOR |
| 0001 | true | WHITE |
| 0002 | false | WHITE |
| 0003 | true | BLACK |
| 0004 | false | BLACK |
| 0005 | true | WHITE |
| 0006 | false | WHITE |
----------------------------------
---------------------
| ID | COND |
| 0001 | jam |
| 0002 | bread |
| 0003 | jam |
| 0004 | bread |
| 0005 | spoon |
| 0006 | spoon |
----------------------
What I want to do is create a VIEW wherein
foreach distinct COND in tbl_2, i would count the number of IDs where tbl_1.COLOR is WHITE
Expected result:
-------------------------------------------
| COND | NUMBER OF ROWS WITH WHITE TAG |
| jam | 1 |
| bread | 1 |
| spoon | 2 |
-------------------------------------------[/font]
Select b.COND, COUNT(a.COLOR) As Count From Ex as a
JOIN Ex1 as b ON a.ID = b.ID
Where a.COLOR = 'White'
Group By b.COND
April 26, 2012 at 4:19 am
Thanks so much! I have learned so much today!
Although I'm still trying to understand how to see it through the columns and not through the rows.
I'll do my best to understand that. I'll look for reading materials about that. 🙂
*btw: Is there a way to boost your point up thru your answer? I'm kinda new here in this forum.:-)
April 26, 2012 at 4:31 am
brally123 (4/26/2012)
*btw: Is there a way to boost your point up thru your answer? I'm kinda new here in this forum.:-)
Nope, the points are pretty meaningless here. You get 1 per post, regardless of whether it's a question or answer (There are points awarded for the question of the day as well). It keeps the atmosphere friendly, which is something that is lacking on many other professional forums.
April 26, 2012 at 4:40 am
April 26, 2012 at 10:08 am
Cadavre (4/26/2012)
brally123 (4/26/2012)
WOW!!! i'm just amazed on how you guys do this! Thank you so much!That was fantastic!
Buuuut, I'm sorry I forgot to add this:
foreach distinct COND in tbl_2, i would count the number of IDs where tbl_1.COLOR is WHITE, then new column for number of blacks
Expected result:
-------------------------------------------
| COND | NUMBER OF ROWS WITH WHITE TAG | NUMBER OF ROWS WITH BLACK TAG
| jam | 1 | 1
| bread | 1 | 1
| spoon | 2 | 0
-------------------------------------------
I can't see any way just to add this in your answer.
I'm thinking a totally new approach? maybe?
Again, you're still looking at the rows and not the columns.
SELECT a.COND, SUM(b.[WHITE]) AS [NUMBER OF ROWS WITH WHITE TAG],SUM(b.[BLACK]) AS [NUMBER OF ROWS WITH BLACK TAG]
FROM #yourTable2 a
INNER JOIN (SELECT ID,
CASE WHEN COLOR = 'WHITE' THEN 1 ELSE 0 END AS [WHITE],
CASE WHEN COLOR = 'BLACK' THEN 1 ELSE 0 END AS [BLACK]
FROM #yourTable1) b ON a.ID = b.ID
GROUP BY a.COND;
How about without the Table Expression, Cadavre or is there a specific reason for it?
Readability... maybe?
SELECT cond,
SUM(CASE WHEN a.color='white' THEN 1 ELSE 0 END) 'Number with White',
SUM(CASE WHEN a.color='black' THEN 1 ELSE 0 END) 'Number with Black'
FROM #tmp1 a
JOIN #tmp2 b
ON a.id = b.id
GROUP BY
cond
April 26, 2012 at 10:13 pm
brally123 (4/26/2012)
@vinu512Thanks for the answer, but I already followed Cadavre's method.
Again, thanks for helping! 🙂
Regarding the points i mentioned, it's the points[score] under your name. :;-)
No problem.
ALways happy to help and learn something new. 😎
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply