CODEWITHSUNDEEP
asp.netsqlcountsum
codeLearner
codeLearner

Reputation: 49

How to count records from multiple columns using sql and asp.net?

I have a table with 5 columns: ID, Session1, Session2, Session3, Session4. What I want to do is to display the number of same items from each column. Same item can't appear in more than one column.

ID   Session1   Session2   Session3   Session4  
--   --------   --------   --------   --------  
1    Music      Web        Tech       Future  
2    Art        Articles   Search     Introduction  
3    Music      Books      Camera     Phone  
4    Music      Glass      Cup        Future  
5    Art        Books      Tech       Future  
6    Music      Glass      Cup        Phone

I want to display it like this on an asp.net page.

Music: 4
Art: 2
Web: 1
Articles: 1
Books: 2
Glass: 2
Tech: 2
Search: 1
Camera: 1
Cup: 2
Future: 3
introduction: 1
Phone: 2

how would I construct a sql query and display them on asp.net?

Upvotes: 0

Views: 976

Answers (3)

DarLom
DarLom

Reputation: 1110

With SQL Server you could do something like this:

  SELECT Session, COUNT(*) FROM (
  SELECT Session1 AS Session FROM TableName
  UNION ALL
  SELECT Session2 AS Session FROM TableName
  UNION ALL
  SELECT Session3 AS Session FROM TableName
  UNION ALL
  SELECT Session4 AS Session FROM TableName) T1
  GROUP BY Session

It's not pretty, but it should work.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146603

try:

  Select z.Val, Count(*) ValueCount
  From   (Select Distinct session1 val From Table Union
          Select Distinct session2 val From Table Union
          Select Distinct session3 val From Table Union
          Select Distinct session4 val From Table) Z
     Join Table t on Z.Val In (t.Session1, t.Session2, t.Session3, t.Session4)
  Group By z.Val

Upvotes: 0

Tom H
Tom H

Reputation: 47402

You could simply use:

SELECT
    session1,
    COUNT(*)
FROM
    My_Table
GROUP BY
    session1
UNION ALL
SELECT
    session2,
    COUNT(*)
FROM
    My_Table
GROUP BY
    session2
...

Upvotes: 1

Related Questions