CODEWITHSUNDEEP
c#sqlsql-server
aman girma
aman girma

Reputation: 684

How to Select count of multiple columns

I have this View

-----------------------------------
ID    1stChoice  2Choice  3rdChoice
-----------------------------------
1  -  AA       -   BB    -   CC
2  -  CC       -   BB    -   AA
3  -  AA       -   CC    -   BB
4  -  BB       -   AA    -   CC
5  -  AA       -   CC    -   BB

and I need to show a report of how many times a field is chosen as 1stChoice,2ndChoice and 3rdChoice.

I tried this Query which works perfectly for single column

select 1stChoice,COUNT(*) as 'Count' from myView group by 1stChoice;

1stChoice - Count
-----------------
AA        -   3       
BB        -   1       
CC        -   1

but this is how i want the output look like

------------------------------------
Field -1stChoice  2Choice  3rdChoice
------------------------------------
AA    -  3       -   1    -   1
BB    -  1       -   2    -   2
CC    -  1       -   2    -   2

Is there a wayi can achieve this without writing select 1stChoice,COUNT(*) as 'Count' from myView group by 1stChoice; for each column i have in the view?
i have seen Select multiple counts from one database table in one sql command access and others but none of them solved my problem.

Upvotes: 1

Views: 572

Answers (5)

Mano
Mano

Reputation: 790

You can try the following query.

 SELECT t1.1stChoice AS Field,
  (SELECT COUNT(*) FROM myView t2 WHERE t2.1stChoice= t1.1stChoice) 1stChoice,
  (SELECT COUNT(*) FROM myView t3 WHERE t3.2Choice  = t1.1stChoice) 2Choice  ,
  (SELECT COUNT(*) FROM myView t4 WHERE t4.3rdChoice= t1.1stChoice ) 3rdChoice
FROM myView t1
GROUP BY t1.1stChoice

Upvotes: 0

jdweng
jdweng

Reputation: 34421

using c# linq :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication98
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("1stChoice", typeof(string));
            dt.Columns.Add("2Choice", typeof(string));
            dt.Columns.Add("3rdChoice", typeof(string));

            dt.Rows.Add(new object[] { 1, "AA", "BB", "CC" });
            dt.Rows.Add(new object[] { 2, "CC", "BB", "AA" });
            dt.Rows.Add(new object[] { 3, "AA", "CC", "BB" });
            dt.Rows.Add(new object[] { 4, "BB", "AA", "CC" });
            dt.Rows.Add(new object[] { 5, "AA", "CC", "BB" });

            string[] fields = dt.AsEnumerable().SelectMany(x => x.ItemArray.Skip(1).Select(y => (string)y)).Distinct().OrderBy(x => x).ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Field", typeof(string));
            pivot.Columns.Add("1stChoice", typeof(string));
            pivot.Columns.Add("2Choice", typeof(string));
            pivot.Columns.Add("3rdChoice", typeof(string));

            foreach (string field in fields)
            {
                int firstChoice = dt.AsEnumerable().Where(x => x.Field<string>("1stChoice") == field).Count();
                int secondChoice = dt.AsEnumerable().Where(x => x.Field<string>("2Choice") == field).Count();
                int thirdChoice = dt.AsEnumerable().Where(x => x.Field<string>("3rdChoice") == field).Count();
                pivot.Rows.Add(new object[] { field, firstChoice, secondChoice, thirdChoice }); 
            }

        }
    }

}

Upvotes: 0

Adam
Adam

Reputation: 4580

Example using pivot;

SELECT [ChosenValue], [pvt].[1stChoice], pvt.[2ndChoice], pvt.[3rdChoice]
FROM (
    SELECT Id, '1stChoice' Choice, [1stChoice] [ChosenValue]
    FROM MyView
    UNION
    SELECT Id, '2ndChoice' Choice, [2ndChoice]
    FROM MyView
    UNION
    SELECT Id, '3rdChoice' Choice, [3rdChoice]
    FROM MyView
) AS tbl
PIVOT (
    COUNT(Id)
    FOR Choice IN ([1stChoice], [2ndChoice], [3rdChoice])
) as pvt

Results;

ChosenValue 1stChoice   2ndChoice   3rdChoice
AA          3           1           1
BB          1           2           2
CC          1           2           2

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can need to do unpivot & pivot : 

SELECT Field, 
       SUM(CASE WHEN choice = '1stChoice' THEN 1 ELSE 0 END) AS [1stChoice],
       SUM(CASE WHEN choice = '2Choice' THEN 1 ELSE 0 END) AS [2stChoice],
       SUM(CASE WHEN choice = '3rdChoice' THEN 1 ELSE 0 END) AS [3stChoice]
FROM myView mv CROSS APPLY
     ( VALUES ('1stChoice', [1stChoice]), ('2Choice', [2Choice]), ('3rdChoice', [3rdChoice])
     ) mvv (choice, Field)
GROUP BY Field;

Upvotes: 0

EzLo
EzLo

Reputation: 14189

You can try something like the following, although performance might spike if you have many records because of the join with multiple OR, and definitely not good for scaling if you plan to have many possible choices (not answers).

IF OBJECT_ID('tempdb..#myView') IS NOT NULL
    DROP TABLE #myView

CREATE TABLE #myView (
    ID INT IDENTITY,
    [1stChoice] VARCHAR(10),
    [2ndChoice] VARCHAR(10),
    [3rdChoice] VARCHAR(10))

INSERT INTO #myView (
    [1stChoice],
    [2ndChoice],
    [3rdChoice])
VALUES
    ('AA', 'BB', 'CC'),
    ('CC', 'BB', 'AA'),
    ('AA', 'CC', 'BB'),
    ('BB', 'AA', 'CC'),
    ('AA', 'CC', 'BB')


;WITH AvailableAnswers AS
(
    SELECT T.[1stChoice] AS Field FROM #myView AS T
    UNION
    SELECT T.[2ndChoice] FROM #myView AS T
    UNION
    SELECT T.[3rdChoice] FROM #myView AS T
)
SELECT
    Field = A.Field,
    [1stChoice] = COUNT(CASE WHEN V.[1stChoice] = A.Field THEN 1 END),
    [2ndChoice] = COUNT(CASE WHEN V.[2ndChoice] = A.Field THEN 1 END),
    [3rdChoice] = COUNT(CASE WHEN V.[3rdChoice] = A.Field THEN 1 END)
FROM
    AvailableAnswers AS A
    INNER JOIN #MyView AS V ON 
        A.Field = V.[1stChoice] OR
        A.Field = V.[2ndChoice] OR
        A.Field = V.[3rdChoice]
GROUP BY
    A.Field

Result:

Field   1stChoice   2ndChoice   3rdChoice
AA      3           1           1
BB      1           2           2
CC      1           2           2

Upvotes: 2

Related Questions