Requesting help with complicated aggregation of a table

I am moving our Databases from My SQL to SQL Server 2000 and am now starting to use views to access data. Very nice feature and worth the upgrade. I need to rewrite a report that we currently have as an Excel file that aggregates tinyint data that has p
ossible of 1-9, * (invalid) and " " (space). The way I am trying to do this in MySQL is like this:
SELECT con_id, count(q2*) (q3*) (q4*)
FROM db_data where con_id in (select con_id FROM congregs where syn_id = "1A")
GROUP BY q2, q3, q4, con_id
The following SQL Statement works with the following output but have not yet been able to get the above statement working
SELECT q3, count(*)
FROM db_data
WHERE con_id=1234567
GROUP BY q3
-- Output --
q3 count(*)
7
*1
14
238
33
I have 415 columns in the db_data table and am wondering if there is any way to write a query that does the above to the entire table (or a selection of rows "from row # to row #). To further compound the problem (and greater sweeten a solution) the poss
ible outputs change depending upon the question. So in q3, in addition to the * and blank possibilities, there can be 1, 2 or 3; in q133 there is a possible 1-7 plus the * and blank.
I can not get the above query to work in SQL Server, only MySQL.
Any help would be welcomed
Thank you
mchemnitz,
Do you mean the following?
SELECT con_id, count(q2), count(q3), count (q4)
FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
GROUP BY con_id
ORDER BY con_id, q2, q3, q4
The above is COUNT(ALL col), but you might prefer COUNT (DISTINCT col).
Russell Fields
"mchemnitz" <mchemnitz@.discussions.microsoft.com> wrote in message
news:17E24782-1BC0-40C3-BCE3-EB2ED78F57D0@.microsoft.com...
> I am moving our Databases from My SQL to SQL Server 2000 and am now
starting to use views to access data. Very nice feature and worth the
upgrade. I need to rewrite a report that we currently have as an Excel file
that aggregates tinyint data that has possible of 1-9, * (invalid) and " "
(space). The way I am trying to do this in MySQL is like this:
> SELECT con_id, count(q2*) (q3*) (q4*)
> FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
> GROUP BY q2, q3, q4, con_id
> The following SQL Statement works with the following output but have not
yet been able to get the above statement working
> SELECT q3, count(*)
> FROM db_data
> WHERE con_id=1234567
> GROUP BY q3
> -- Output --
> q3 count(*)
> 7
> * 1
> 1 4
> 2 38
> 3 3
> I have 415 columns in the db_data table and am wondering if there is any
way to write a query that does the above to the entire table (or a selection
of rows "from row # to row #). To further compound the problem (and greater
sweeten a solution) the possible outputs change depending upon the question.
So in q3, in addition to the * and blank possibilities, there can be 1, 2 or
3; in q133 there is a possible 1-7 plus the * and blank.
> I can not get the above query to work in SQL Server, only MySQL.
> Any help would be welcomed
> Thank you