Count a specific value from multiple columns and group by values in another column... in mysql

Hey. I have 160 columns that are filled with data when a user fills a report form out and submit it. A few of these sets of columns contain similar data, but there needs to be multiple instance of this data per record set as it may be different per instance in the report.

For example, an employee opens a case by a certain type at one point in the day, then at another point in the day they open another case of a different type. I want to create totals per user based on the values in these columns. There is one column set that I want to target right now, case type. I would like to be able to see all instances of the value "TSTO" in columns CT1, CT2, CT3... through CT20. Then have that sorted by the employee ID number, which is just one column in the table.

Any ideas? I am struggling with this one.

So far I have SELECT CT1, CT2, CT3, CT4, CT5, CT6, CT7, CT8, CT9, CT10, CT11, CT12, CT13, CT14, CT15, CT16, CT17, CT18, CT19, CT20 FROM REPORTS GROUP BY OFFICER

This will display the values of all the case type entries in a record set but I need to count them, I tried to use,

SELECT CT1, CT2, CT3, CT4, CT5, CT6, CT7, CT8, CT9, CT10, CT11, CT12, CT13, CT14, CT15, CT16, CT17, CT18, CT19, CT20 FROM REPORTS COUNT(TSTO) GROUP BY OFFICER

but it just spits an error. I am fairly new to mysql databasing and php, I feel I have a good grasp but query'ing the database and the syntax involved is a tad bit confused and/or overwhelming right now. Just gotta learn the language. I will keep looking and I have found some similar things on here but I don't understand what I am looking at (completely) and I would like to shy away from using code that "works" but I don't understand fully.

Thank you very much :)

Edit -

So this database is an activity report server for the days work for the employees. The person will often open cases during the day. These cases vary in type, and their different types are designated by a four letter convention. So your different case types could be TSTO, DOME, ASBA, etc etc. So the user will fill out their form throughout the day then submit it down to the database. That's all fine :) Now I am trying to build a page which will query the database by user request for statistics of a user's activities. So right now I am trying to generate statistics. Specifically, I want to be able to generate the statistic of, and in human terms, "HOW MANY OCCURENCES OF "USER INPUTTED CASE TYPE" ARE THERE FOR EMPLOYEEIDXXX"

So when a user submits a form they will type in this four letter case type up to 20 times in one form, there is 20 fields for this case type entry, thus there is 20 columns. So these 20 columns for case type will be in one record set, one record set is generated per report. Another column that is generated is the employeeid column, which basically identifies who generated the record set through their form.

So I would like to be able to query all 20 columns of case type, across all record sets, f开发者_JS百科or a defined type of case (TSTO, DOME, ASBA, etc etc) and then group that to corresponding user(s).

So the output would look something like,

316 TSTO's for employeeid108

I hope this helps to clear it up a bit. Again I am fairly fresh to all of this so I am not the best with the vernacular and best practices etc etc...

Thanks so much :)

Edit 2 -

So to further elaborate on what I have going on, I have an HTML form that has 164 fields. Each of these fields ultimately puts a value into a column in a single record set in my DB, each submission. I couldn't post images or more than two URLs so I will try to explain it the best I can without screenshots.

So what happens is this information gets in the DB. Then there is the query'ing. I have a search page which uses an HTML form to select the type of information to be searched for. It then displays a synopsis of each report that matches the query. The user than enters the REPORT ID # for the report they want to view in full into another small form (an input field with a submit button) which brings them to a page with the full report displayed when they click submit.

So right now I am trying to do totals and realizing my DB will be needing some work and tweaking to make it easier to create querys for it for different information needed. I've gleaned some good information so far and will continue to try and provide concise information about my setup as best I can.

Thanks.

Edit 3 -

Maybe you can go to my photobucket and check them out, should let me do one link, there is five screenshots, you can kind of see better what I have happening there.

http://s1082.photobucket.com/albums/j376/hughessa

:)

---

The query you are looking for would be very long and complicated for your current db schema.

Every table like (some_id, column1, column2, column3, column4... ) where columns store the same type of data can be also represented by a table (some_id, column_number, column_value ) where instead of 1 row with values for 20 columns you have 20 rows.

So your table should rather look like:

officer     ct_number     ct_value
1           CT1           TSTO
1           CT2           DOME
1           CT3           TSTO
1           CT4           ASBA
(...)
2           CT1           DOME
2           CT2           TSTO

For a table like this if you wanted to find how many occurences of different ct_values are there for officer 1 you would use a simple query:

SELECT officer, ct_value, count(ct_value) AS ct_count 
  FROM reports WHERE officer=1 GROUP BY ct_value

giving results

officer   ct_value    ct_count   
1         TSTO        2
1         DOME        1
1         ASBA        1

If you wanted to find out how many TSTO's are there for different officers you would use:

SELECT officer, ct_value, count( officer ) as ct_count FROM reports
  WHERE ct_value='TSTO' GROUP BY officer

giving results

 officer  ct_value  ct_count
 1        TSTO      2
 2        TSTO      1

Also any type of query for your old schema can be easily converted to new schema.

However if you need store additional information about every particular report I suggest having two tables:

Submissions 
submission_id    report_id   ct_number  ct_value
primary key      
auto-increment
------------------------------------------------
1                1           CT1        TSTO
2                1           CT2        DOME
3                1           CT3        TSTO
4                1           CT4        ASBA
5                2           CT1        DOME
6                2           CT2        TSTO  

with report_id pointing to a record in another table with as many columns as you need for additional data:

Reports
report_id      officer     date                 some_other_data
primary key
auto-increment
--------------------------------------------------------------------
1              1           2011-04-29 11:28:15  Everything went ok
2              2           2011-04-29 14:01:00  There were troubles

Example: How many TSTO's are there for different officers:

SELECT r.officer, s.ct_value, count( officer ) as ct_count 
FROM submissions s JOIN reports r ON s.report_id = r.report_id
WHERE s.ct_value='TSTO' 
GROUP BY r.officer