Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pauley1968
Starting Member
9 Posts |
 Posted - 2009-12-03 : 12:44:15
|
| Hello,I have a table that has a company name, date, and 2 fields that track answers to 2 questions. The 2 questions each have 3 possible answers with a value of 1,2 or 3. Looks something like below.companyname question1 question2 dateComA 1 2 11/23/2009ComA 1 1 11/24/2009ComA 3 2 11/25/2009ComB 3 1 11/24/2009ComB 1 3 11/25/2009ComB 3 1 11/26/2009What I'm trying to do is print a report to screen that shows a summary of the answers for each company. See below. Company Answer1 Answer2 Answer3 Answer1 Answer2 Answer3 (1's) (2's) (3's) (1's) (2's) (3's)ComA 2 0 1 1 2 0ComB 1 0 2 2 0 1I've tried combinations of Distinct, Count and Group By with no luck. I'm sure there is a Select that will do it but I can't figure it out. Any help would be greatly appreciated! Please help! |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-03 : 13:41:01
|
Try this..select companyname,sum(case when question1 = 1 then 1 else 0 end) as [Q1Ans1],sum(case when question1 = 2 then 1 else 0 end) as [Q1Ans2],sum(case when question1 = 3 then 1 else 0 end) as [Q1Ans3],sum(case when question2 = 1 then 1 else 0 end) as [Q2Ans1],sum(case when question2 = 2 then 1 else 0 end) as [Q2Ans2],sum(case when question2 = 3 then 1 else 0 end) as [Q2Ans3]from table1group by companyname |
 |
|
|
Pauley1968
Starting Member
9 Posts |
Posted - 2009-12-07 : 00:06:37
|
| Hey man, Thanks very much! Worked like a charm! U da man! |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-07 : 09:49:36
|
| Np..You're welcome. |
|
|
|
Pauley1968
Starting Member
9 Posts |
Posted - 2009-12-29 : 12:51:46
|
Hello there,A while back you helped me with the select in the quote below. Once again, thanks very much! I took it and applied it to my page and turned it into the select below. strSQL1 = "select Convert(varchar(10),loggedon,101),sum(case when feel = 1 then 1 else 0 end) as [Q1Ans1],sum(case when feel = 2 then 1 else 0 end) as [Q1Ans2],sum(case when feel = 3 then 1 else 0 end) as [Q1Ans3],sum(case when action = 1 then 1 else 0 end) as [Q2Ans1],sum(case when action = 2 then 1 else 0 end) as [Q2Ans2],sum(case when action = 3 then 1 else 0 end) as [Q2Ans3] from table group by Convert(varchar(10),loggedon,101) ORDER BY Convert(varchar(10),loggedon,101) ASC" I needed a report that gave a daily summary. The select works, however, it doesn't sort my dates quite right. It shows 12/1/2009 and then 12/10/2009 instead of 12/2/2009.I also tried the same select with ORDER BY Convert(datetime,loggedon,101) ASC, however that just made the select not work anymore. Any ideas?Any additional assistance you could offer would be greatly appreciated!quote:
Originally posted by vijayisonly
Try this..select companyname,sum(case when question1 = 1 then 1 else 0 end) as [Q1Ans1],sum(case when question1 = 2 then 1 else 0 end) as [Q1Ans2],sum(case when question1 = 3 then 1 else 0 end) as [Q1Ans3],sum(case when question2 = 1 then 1 else 0 end) as [Q2Ans1],sum(case when question2 = 2 then 1 else 0 end) as [Q2Ans2],sum(case when question2 = 3 then 1 else 0 end) as [Q2Ans3]from table1group by companyname
|
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-12-29 : 13:07:37
|
| is loggedon datetime or varchar? why is the data saved as 12/1/2009 instead of 12/01/2009?<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-29 : 13:55:58
|
Here's a way..this is at best a hack...strSQL1 = "select Convert(varchar(10),loggedon,101),sum(case when feel = 1 then 1 else 0 end) as [Q1Ans1],sum(case when feel = 2 then 1 else 0 end) as [Q1Ans2],sum(case when feel = 3 then 1 else 0 end) as [Q1Ans3],sum(case when action = 1 then 1 else 0 end) as [Q2Ans1],sum(case when action = 2 then 1 else 0 end) as [Q2Ans2],sum(case when action = 3 then 1 else 0 end) as [Q2Ans3] from table group by Convert(varchar(10),loggedon,101),dateadd(d, datediff(d, 0, loggedon), 0)order by dateadd(d, datediff(d, 0, loggedon), 0)" |
|
|
|
Pauley1968
Starting Member
9 Posts |
Posted - 2009-12-29 : 17:11:35
|
| loggedon is a varchar and that is just because I was told I wouldn't need to use it in a functional sort of way. So by default the date being submitted is in the form of 11/25/2009 2:24:45 AM.Then when I needed a report that summarized each day I started using Convert(varchar(10),loggedon,101) to eliminate the Time part of the date so I could group an entire day. Otherwise there are no 2 dates the same because of the time.So now that is working but the sorting is a problem. I haven't yet but I am going to try vijayisonly's post and see if that works. I hope that explains things better though! |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-29 : 17:16:19
|
| Its better to change your column to a datetime. It is so much easier to work with dates then.But..yes...that solution should work on your current data type. |
|
|
|
Pauley1968
Starting Member
9 Posts |
Posted - 2009-12-29 : 17:34:37
|
| Yeah it is much easier to use datetime had they told me this from the beginning. However, your work around did the trick just fine!Thanks once again man! I really appreciate the help! |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-12-29 : 21:31:29
|
| Np..You're welcome. |
|
|
|
|
|
|
|
|
Noob needs help with Distinct/Count/Group By!