March 25, 2010 at 1:02 pm
I have a table, Answer, with the following columns
ID int PK Identity
Q1 int null,
Q2 int null,
Q3 int null
the data inside the columns range from 1 to 7
example
Answer
---------
Q1 Q2 Q3
---------
1 5 2
4 1 3
7 1 2
1 3 1
1 1 2
I need a query that will return the number of 1's,2's,3's,4's,5's,6's,7's
for for each column. for example.
Q1 Q2 Q3
1 3 2 1
2 0 0 3
3 0 1 1
4 1 0 0
5 0 1 0
6 0 0 0
7 1 0 0
The above result reads
There are
three 1's for Q1, two 1's for Q2, one 1 for Q3
zero 2's for Q1, zero 2's for Q2, three 2's for Q3
the 1st column list all the possible values (1 thru 7)
the other columns list the counts of each value for
that column.
I've been trying various query but none returns the grid I'm looking for.
"Help me, Obi-Wan Kenobi, you're my only hope"
thanks,
P
March 25, 2010 at 1:12 pm
Try this out:
DECLARE @Table TABLE (ID int IDENTITY(1,1) PRIMARY KEY, Q1 int, Q2 int, Q3 int)
INSERT INTO @Table(Q1, Q2, Q3)
SELECT 1, 5, 2 UNION ALL
SELECT 4, 1, 3 UNION ALL
SELECT 7, 1, 2 UNION ALL
SELECT 1, 3, 1 UNION ALL
SELECT 1, 1, 2
SELECT N,
SUM(CASE Q1 WHEN N THEN 1 ELSE 0 END) as Q1,
SUM(CASE Q2 WHEN N THEN 1 ELSE 0 END) as Q2,
SUM(CASE Q3 WHEN N THEN 1 ELSE 0 END) as Q3
FROM@Table t
CROSS JOIN (
SELECT 1 as N UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7
) as t2
GROUP BY N
March 25, 2010 at 1:14 pm
By the way, if you have a tally or numbers table, you could easily replace the derived table (t2) with the tally/numbers table and use WHERE N BETWEEN 1 AND 7 in the where clause.
March 25, 2010 at 1:17 pm
Psion (3/25/2010)
I have a table, Answer, with the following columnsID int PK Identity
Q1 int null,
Q2 int null,
Q3 int null
the data inside the columns range from 1 to 7
example
Answer
---------
Q1 Q2 Q3
---------
1 5 2
4 1 3
7 1 2
1 3 1
1 1 2
I need a query that will return the number of 1's,2's,3's,4's,5's,6's,7's
for for each column. for example.
Q1 Q2 Q3
1 3 2 1
2 0 0 3
3 0 1 1
4 1 0 0
5 0 1 0
6 0 0 0
7 1 0 0
The above result reads
There are
three 1's for Q1, two 1's for Q2, one 1 for Q3
zero 2's for Q1, zero 2's for Q2, three 2's for Q3
the 1st column list all the possible values (1 thru 7)
the other columns list the counts of each value for
that column.
I've been trying various query but none returns the grid I'm looking for.
"Help me, Obi-Wan Kenobi, you're my only hope"
thanks,
P
John's solution will definitely work, but I have a suggestion for Psion.
Your post SCREAMS homework or test question. I STRONGLY suggest you understand what John has done. And for future reference, post what you've tried with your questions. it helps us understand your knowledge level and we can pose the answers to fit it... 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 25, 2010 at 1:18 pm
Thanks for the quick reply. Even though this query ran without any errors, I need the data to be pulled from my table, Answer. I don't know what to modify to make your query execute against my table which is called, Answer.
thanks again,
P
March 25, 2010 at 1:18 pm
Here is the solution using an existing tally table. John just happened to be faster than me this time.
create table #TestTable (
ID int,
Q1 int,
Q2 int,
Q3 int
);
insert into #TestTable
select 1,1,5,2 union all
select 2,4,1,3 union all
select 3,7,1,2 union all
select 4,1,3,1 union all
select 5,1,1,2
;
select
N,
sum(case when Q1 = N then 1 else 0 end),
sum(case when Q2 = N then 1 else 0 end),
sum(case when Q3 = N then 1 else 0 end)
from
#TestTable
cross join dbo.Tally
where
N between 1 and 7
group by
N;
March 25, 2010 at 1:20 pm
Psion (3/25/2010)
Thanks for the quick reply. Even though this query ran without any errors, I need the data to be pulled from my table, Answer. I don't know what to modify to make your query execute against my table which is called, Answer.thanks again,
P
Quite simple, substitute your table for John's table variable or my temporary table.
This post, by the way, screams even more that this is homework for a class.
March 25, 2010 at 1:21 pm
Psion (3/25/2010)
Thanks for the quick reply. Even though this query ran without any errors, I need the data to be pulled from my table, Answer. I don't know what to modify to make your query execute against my table which is called, Answer.thanks again,
P
Previous post proven! ... *grin*
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 25, 2010 at 1:22 pm
This is not a test question. I am coding at home building an asp.net website and this is my part of my data access layer. some things I've tried are.
select
case
when q1 = 1 then 'q1 - 1'
when q1 = 2 then 'q1 - 2'
when q1 = 3 then 'q1 - 3'
when q1 = 4 then 'q1 - 4'
when q1 = 5 then 'q1 - 5'
when q2 = 1 then 'q2 - 1'
when q2 = 2 then 'q2 - 2'
when q2 = 3 then 'q2 - 3'
when q2 = 4 then 'q2 - 4'
when q2 = 5 then 'q2 - 5'
end as 'Answer',
count(*) as 'Total'
from dbo.Answer
group by
case
when q1 = 1 then 'q1 - 1'
when q1 = 2 then 'q1 - 2'
when q1 = 3 then 'q1 - 3'
when q1 = 4 then 'q1 - 4'
when q1 = 5 then 'q1 - 5'
when q2 = 1 then 'q2 - 1'
when q2 = 2 then 'q2 - 2'
when q2 = 3 then 'q2 - 3'
when q2 = 4 then 'q2 - 4'
when q2 = 5 then 'q2 - 5'
end
and
SELECT Q1, Record_Count=Count(*)
FROM Answer
GROUP BY Q1
the above query gets correct results just for the Q1 table (obviously).
-Psion
March 25, 2010 at 1:23 pm
Oh, and since this looks like homework, you may want to use John's solution as I'm sure mine won't work as you probably don't have a tally table.
March 25, 2010 at 1:23 pm
Did you notice that your sample data and your expected result don't match?
You could verify the result if you'd aggregate the three columns. The result should match the number of rows you provided as sample data... 😉
March 25, 2010 at 1:23 pm
And Psion, don't take my comments ........{edited}........ as attacking.
I/we encourage you to use this site to help in your learning. It can be an invaluable resource, just don't abuse it. 😉
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 25, 2010 at 1:25 pm
SELECT N,
SUM(CASE Q1 WHEN N THEN 1 ELSE 0 END) as Q1,
SUM(CASE Q2 WHEN N THEN 1 ELSE 0 END) as Q2,
SUM(CASE Q3 WHEN N THEN 1 ELSE 0 END) as Q3
FROM Answer t
CROSS JOIN (
SELECT 1 as N UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7
) as t2
GROUP BY N
March 25, 2010 at 1:27 pm
Lynn Pettis (3/25/2010)
Oh, and since this looks like homework, you may want to use John's solution as I'm sure mine won't work as you probably don't have a tally table.
Actually, they do. I thought this at first as well. But where Psion is saying "The above results read..." is referring to the dataset ABOVE the previous dataset...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 25, 2010 at 1:55 pm
Lynn Pettis (3/25/2010)
Here is the solution using an existing tally table. John just happened to be faster than me this time.
.....doesn't happen often 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply