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
Medium Priority
241 Views
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
Question by:techpr0

LVL 18

Accepted Solution

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

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

## Featured Post

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. …
###### Suggested Courses
Course of the Month10 days, 7 hours left to enroll