Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

I need help with writing a select statement that totals a company survey answers.

Posted on 2011-10-31
2
Medium Priority
?
241 Views
Last Modified: 2012-05-12
I have the following table. where the data will only be 1,2 or 3.

name                q1       q2      q3    
Company 1        1         2       3      
Company 1        1         3       1        
Company 1        1         2       3        
Company 2        2         2       3        
Company 2        1         1       3        
Company 3        3         2       3        
Company 4        1         2       3        

I need to run a query that will total the response grouped by Company. So It will return the following

name            q1one   q1two    q1three   q2one    q2two  q2three   q3one    q3two   q3three
Company 1          3       0            0           0            2          1           1           0            2
Company 2          1       1            0           1            1          0           0           0            2
Company 3          0       0            1           0            1          0           0           0            1
Company 4          1       0            0           0            1          0           0           0            1

So basically I am summing up the results to know that company 1 entered 1 for column q1 3 times, 2 in column q1 0 times for company 1, 3 in column q1 0 times for company 1, and so on
0
Comment
Question by:techpr0
2 Comments
 
LVL 18

Accepted Solution

by:
lludden earned 2000 total points
ID: 37057448
SELECT Name,
SUM(CASE WHEN Q1 = 1 THEN 1 ELSE 0 END) AS Q1One,
SUM(CASE WHEN Q1 = 2 THEN 1 ELSE 0 END) AS Q1Two,
SUM(CASE WHEN Q1 = 3 THEN 1 ELSE 0 END) AS Q1Three,
SUM(CASE WHEN Q2 = 1 THEN 1 ELSE 0 END) AS Q2One,
SUM(CASE WHEN Q2 = 2 THEN 1 ELSE 0 END) AS Q2Two,
SUM(CASE WHEN Q2 = 3 THEN 1 ELSE 0 END) AS Q2Three,
SUM(CASE WHEN Q3 = 1 THEN 1 ELSE 0 END) AS Q3One,
SUM(CASE WHEN Q3 = 2 THEN 1 ELSE 0 END) AS Q3Two,
SUM(CASE WHEN Q3 = 3 THEN 1 ELSE 0 END) AS Q3Three
FROM MyTable
GROUP BY Name
0
 

Author Closing Comment

by:techpr0
ID: 37057639
Sweet!!! Just what I needed. Thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question