Data count from different columns in database

Question

I have got a table called tblCar and there are ID, CarNumber and other 32 fields. 32 columns are filled as s1,s2,s3,.......,s32 since they are dedicated to remark whether it is for SEATS which are available(A), booking(B), and Sold(S).

I want to count available seats, booking seats and sold out seats from that database. how should i write a query?

Is there anyway to count from string S, B and A from 32 different columns in database?

What i want to show is like that..... S = 20, B = 10, A = 2;

SqlCommand cmd = new SqlCommand(
  "Select count(*) 
   FROM tblCar 
   WHERE s1= 'S' or s2= 'S' or s3= 'S' 
   or s4= 'S' or s5= 'S' or s6= 'S' 
   or s7= 'S' or s8= 'S' or s9= 'S' 
   or s10= 'S' or s11= 'S' or s12= 'S' 
   or s13= 'S' or s14= 'S' or s15= 'S' 
   or s16= 'S' or s17= 'S' or s18= 'S' 
   or s19= 'S' or s20= 'S' or s21= 'S' 
   or s22= 'S' or s23= 'S' or s24= 'S' 
   or s25= 'S' or s26= 'S' or s27= 'S' 
   or s28= 'S' or s29= 'S' or s30= 'S' 
   or s31= 'S' or s32= 'S' ", con
); 
count += cmd.ExecuteNonQuery(); 

this is how I work for "Sold" field.but it showed only count = -128.i did initialized count as 0

Answer 1

Try this sql query:

SELECT SUM(CASE WHEN State = 'S' THEN 1 ELSE 0 END) AS Sold
, SUM(CASE WHEN State = 'B' THEN 1 ELSE 0 END) AS Booked
, SUM(CASE WHEN State = 'A' THEN 1 ELSE 0 END) AS Available
FROM 
    (SELECT S1, S2, S3, S4, S5, S6
    FROM Seats) s
UNPIVOT
    (State FOR Seat IN (S1, S2, S3, S4, S5, S6)) AS rows;

Here SQL Fiddle.

In example I used only 6 columns, but in your case you need set for all 32 columns, but only one time

C# code:

using SqlConnection conn = new SqlConnection(yourConnectionString)
{
    StringBuilder query = new StringBuilder();
    query.AppendLine("SELECT SUM(CASE WHEN State = 'S' THEN 1 ELSE 0 END) AS Sold");
    query.AppendLine(", SUM(CASE WHEN State = 'B' THEN 1 ELSE 0 END) AS Booked");
    query.AppendLine(", SUM(CASE WHEN State = 'A' THEN 1 ELSE 0 END) AS Available");
    query.AppendLine("FROM ");
    query.AppendLine("(SELECT S1, S2, S3, S4, S5, S6, S7, S8");
    query.AppendLine("(,S9, S10, S11, S12, S13, S14, S15, S16");
    query.AppendLine("(,S17, S18, S19, S20, S21, S22, S23, S24");
    query.AppendLine("(,S25, S26, S27, S28, S29, S30, S31, S32");
    query.AppendLine("FROM Seats) s");
    query.AppendLine("UNPIVOT");
    query.AppendLine("(State FOR Seat IN (S1, S2, S3, S4, S5, S6, S7, S8");
    query.AppendLine("(,S9, S10, S11, S12, S13, S14, S15, S16");
    query.AppendLine("(,S17, S18, S19, S20, S21, S22, S23, S24");
    query.AppendLine("(,S25, S26, S27, S28, S29, S30, S31, S32)) AS rows;");

    conn.Open();
    using SqlCommand command = new SqlCommand(query.ToString(), conn)
    {
        DataTable data = new DataTable();
        data.Load(command.ExecuteReader());
        //then get your values
        Int32 avialable = 0;
        Int32 booked= 0;
        Int32 sold = 0;
        if(data.Rows.Count > 0)
        {
            available = (Int32)data(0)("Available");
            booked = (Int32)data(0)("Booked");
            sold = (Int32)data(0)("Sold");
        }           
    }
}

Answer 2

You should use ExecuteScalar if you want it to return a value.

SqlCommand cmd = new SqlCommand("Select count(*) FROM tblCar WHERE s1= 'S' or s2= 'S' or s3= 'S' or s4= 'S' or s5= 'S' or s6= 'S' or s7= 'S' or s8= 'S' or s9= 'S' or s10= 'S' or s11= 'S' or s12= 'S' or s13= 'S' or s14= 'S' or s15= 'S' or s16= 'S' or s17= 'S' or s18= 'S' or s19= 'S' or s20= 'S' or s21= 'S' or s22= 'S' or s23= 'S' or s24= 'S' or s25= 'S' or s26= 'S' or s27= 'S' or s28= 'S' or s29= 'S' or s30= 'S' or s31= 'S' or s32= 'S' ", con); 
count += (int)cmd.ExecuteScalar();

Answer 3

Since you are returning 1 value you should call .ExecuteScalar. Upi will also need to Convert the return to an int.

count = Convert.ToInt32(sqlcmd01.ExecuteScalar());

From MSDN

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

You are only looking for 1 value so simply call cmd.ExecuteScalar(); ExecuteNonQuery() and ExecuteReader() are better for other things. (Ex. ExecuteNoNQuery() for INSERTS)

This SO Link sums up the differences very well.

Answer 4

You have a pretty nasty table structure and I would look at changing that, but with what you've got you can do this:

    SELECT SUM(CASE s1 WHEN 'S' THEN 1 ELSE 0 END) as sold,
     SUM(CASE s1 WHEN 'A' THEN 1 ELSE 0 END) as available,
     SUM(CASE s1 WHEN 'B' THEN 1 ELSE 0 END) as booked,
   FROM tblCar 
   UNION
    SELECT SUM(CASE s2 WHEN 'S' THEN 1 ELSE 0 END),
     SUM(CASE s2 WHEN 'A' THEN 1 ELSE 0 END),
     SUM(CASE s2 WHEN 'B' THEN 1 ELSE 0 END),
   UNION
    ... Repeat the above for each field.... yuck!...
   UNION
   SELECT SUM(CASE s32 WHEN 'S' THEN 1 ELSE 0 END),
     SUM(CASE s32 WHEN 'A' THEN 1 ELSE 0 END),
     SUM(CASE s32 WHEN 'B' THEN 1 ELSE 0 END)

Answer 5

My personal suggestion is a redesign of the tables. Use only 3 columns, 'available', 'sold' and 'Booking'. Each column holds and int (bigint maybe?), that int will hold the status of all the seats that have that condition as a binary code. For instance, if seats 1,2,3 & 4 are the only 'sold' seats that value becomes 1+2+4+8 = 17. if only seats 4 & 5 are 'sold' the value would be 8+16 = 24. This way you don't have to query all the columns to find seats sold, you only have to query the 'sold' column and it will tell you which seats are sold .