CODEWITHSUNDEEP
vb.net
user3211476
user3211476

Reputation: 37

i want to retrieve every count specific data in a field

is there any way to do this ? i want to retrieve every count of data in a field

here's my code..

 Dim cmd1 As New SqlCommand("SELECT COUNT(support) AS support " & _
                            "FROM   Studz_Table WHERE support ='others'",conn)
dim int1 as integer = cmd1.executescalar

Dim cmd2 As New SqlCommand("SELECT COUNT(support) AS support " & _
                            "FROM   Studz_Table WHERE support ='myself'",conn)
dim int2 as integer = cmd1.executescalar
Dim cmd3 As New SqlCommand("SELECT COUNT(support) AS support " & _
                            "FROM   Studz_Table WHERE support ='parent'",conn)
dim int3 as integer = cmd1.executescalar

is there any simple way to do this ?

Upvotes: 0

Views: 34

Answers (1)

thudbutt
thudbutt

Reputation: 1521

You can use a combination of SUM and CASE to retrieve counts for each type.

Dim cmd As New SqlCommand("SELECT SUM(CASE WHEN support = 'others' THEN 1 ELSE 0 END) AS OthersCount, " & _
                          "SUM(CASE WHEN support = 'myself' THEN 1 ELSE 0 END) AS MyselfCount, " & _
                          "SUM(CASE WHEN support = 'parent' THEN 1 ELSE 0 END) AS ParentCount " & _
                            "FROM   Studz_Table WHERE support ='others'",conn)

You'll need to use ExecuteReader though instead of ExecuteScalar to access each column:

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
     int int1 = reader[0];
     int int2 = reader[1];
     int int3 = reader[2];
}

Upvotes: 1

Related Questions