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

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 n
eed to rewrite a report that we currently have as an Excel file that aggrega
tes 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
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 s
weeten a solution) the poss
ible outputs change depending upon the question. So in q3, in addition to t
he * and blank possibilities, there can be 1, 2 or 3; in q133 there is a pos
sible 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 youmchemnitz,
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

Req: how to deploy a SQL Client Alias to multiple clients

Hi there,
I'm looking for a quick way to setup/deploy an SQL alias on multiple
clients?
Due to an hw upgrade, we are moving our cluster to a new server, and we need
to setup on multiple clients a new SQL Client Alias and an ODBC System DSN.
I found that Sql Client Alias configuration are stored in a registry key,
but I don't know if exporting the hive from the first client and importing
in the remaining other will be ok
Thanks in advance
---
Silvio Accomando
Torre Informatica Srl
---Use regedit to expot the registry key and then use regedit to apply it to
the client.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Repost: 00000 display on upgrade to Windows XP

I posted this under 'microsoft.public.sqlserver.client' but got no reply.
Any help with this problem would be greatly appreciated
-------
I developed a database under SQL Server 2000, with Access 2000 on Windows
2000 as client. This had been running fine for several years. The client is
now upgrading to Windows XP, and has come across a display problem on the
reports. The figures are correct but are now displayed with lots of trailing
zeros e.g

365.00000

I have built a test system but cannot duplicate the problem :-( . Also the
problem varies between computers, and even between different users on the
same computer. One solution that seems to work is to explicitly cast all
output from the server e.g.

CAST ( TonsLoaded AS INT) TonsLoaded

but this is a pita since there are 141 stored procedures.

Has anyone seen this problem? I haven't found anything in the newsgroups or
any Knowlege Base article. It looks like it is to do with Windows XP as the
client ...

David"David Greenwood" <david at greenwood dot lu> wrote in message
news:40274c80$1_1@.news.vo.lu...
> I posted this under 'microsoft.public.sqlserver.client' but got no reply.
> Any help with this problem would be greatly appreciated
> -------
> I developed a database under SQL Server 2000, with Access 2000 on Windows
> 2000 as client. This had been running fine for several years. The client
is
> now upgrading to Windows XP, and has come across a display problem on the
> reports. The figures are correct but are now displayed with lots of
trailing
> zeros e.g
> 365.00000
> I have built a test system but cannot duplicate the problem :-( . Also
the
> problem varies between computers, and even between different users on the
> same computer. One solution that seems to work is to explicitly cast all
> output from the server e.g.
> CAST ( TonsLoaded AS INT) TonsLoaded
> but this is a pita since there are 141 stored procedures.
> Has anyone seen this problem? I haven't found anything in the newsgroups
or
> any Knowlege Base article. It looks like it is to do with Windows XP as
the
> client ...
> David

If the behaviour varies between users on the same PC, then the issue may be
somehow related to the language or regional settings in their profiles,
although 4 decimal places seems like a strange format. Or possibly the
updated MDAC version in Windows XP is behaving slightly differently. What
data type is the TonsLoaded column in the result set - decimal?

In any case, I guess you should be able to modify your Access client to
display these figures as integers - it's usually easier to handle display
issues in the front end. And you might consider posting this in an Access
newsgroup (if you haven't already), in case it's a known Access/XP issue.

Simon|||If the worse comes to the worse then I shall just have to fix the problem on
the client end, somehow. But this is a big file (10 mega) full of forms and
reports so that will be a major job.

I checked on the client's system and either there are 3 trailing zeros or 6
trailing zeros, though I can't see why. Also, it appears that all the
machines have exactly the same problem. The only difference I can see with
my test system is that they have Active Directory ... (?)

I shall try posting to the Access newsgroup as you suggested.

David

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4027d81a$1_2@.news.bluewin.ch...
> "David Greenwood" <david at greenwood dot lu> wrote in message
> news:40274c80$1_1@.news.vo.lu...
> > I posted this under 'microsoft.public.sqlserver.client' but got no
reply.
> > Any help with this problem would be greatly appreciated
> > -------
> > I developed a database under SQL Server 2000, with Access 2000 on
Windows
> > 2000 as client. This had been running fine for several years. The client
> is
> > now upgrading to Windows XP, and has come across a display problem on
the
> > reports. The figures are correct but are now displayed with lots of
> trailing
> > zeros e.g
> > 365.00000
> > I have built a test system but cannot duplicate the problem :-( . Also
> the
> > problem varies between computers, and even between different users on
the
> > same computer. One solution that seems to work is to explicitly cast all
> > output from the server e.g.
> > CAST ( TonsLoaded AS INT) TonsLoaded
> > but this is a pita since there are 141 stored procedures.
> > Has anyone seen this problem? I haven't found anything in the newsgroups
> or
> > any Knowlege Base article. It looks like it is to do with Windows XP as
> the
> > client ...
> > David
> If the behaviour varies between users on the same PC, then the issue may
be
> somehow related to the language or regional settings in their profiles,
> although 4 decimal places seems like a strange format. Or possibly the
> updated MDAC version in Windows XP is behaving slightly differently. What
> data type is the TonsLoaded column in the result set - decimal?
> In any case, I guess you should be able to modify your Access client to
> display these figures as integers - it's usually easier to handle display
> issues in the front end. And you might consider posting this in an Access
> newsgroup (if you haven't already), in case it's a known Access/XP issue.
> Simon|||David Greenwood (david at greenwood dot lu) writes:
> I posted this under 'microsoft.public.sqlserver.client' but got no reply.
> Any help with this problem would be greatly appreciated
> -------
> I developed a database under SQL Server 2000, with Access 2000 on
> Windows 2000 as client. This had been running fine for several years.
> The client is now upgrading to Windows XP, and has come across a display
> problem on the reports. The figures are correct but are now displayed
> with lots of trailing zeros e.g
> 365.00000
> I have built a test system but cannot duplicate the problem :-( . Also
> the problem varies between computers, and even between different users
> on the same computer. One solution that seems to work is to explicitly
> cast all output from the server e.g.

I have to admit that I saw your post in .clients, but I let it pass, since
1) I don't know Access, 2) There seems to be information missing from
your posting.

What data type are the columns in SQL Server? How does the code look
like in Access to bring the data from SQL Server to the output? Do you
just associate a record set with a window control, or is there some
processing on the way?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I rebuilt my test Windows XP system, managed to duplicate the error, and
have at last got to the bottom of the problem. It would appear that Windows
XP does not correctly interprets the data for fields based on the SQL Server
"decimal" type, even though this has always worked fine under Windows 2000.

We use decimal fields with fixed three decimal places to store tonnes and
kilos. Once these have been totalled, averaged etc they automatically track
6 decimal places of precision. After ROUNDing to 0 places the data is
returned to the client. However, running tests under Query Analyser shows
that the 6 zeros are still part of the decimal answer. Windows 2000 has
never had a problem with this, no matter what the language settings. The
data has displayed okay. In contrast, Windows XP chokes on the data. I
guess not many people use the decimal data type, so this has not been an
issue.

The work round is to CAST the data to either INT or FLOAT as required on the
server.

Thanks for the feedback,

David

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948AF2EB79AB2Yazorman@.127.0.0.1...
> David Greenwood (david at greenwood dot lu) writes:
> > I posted this under 'microsoft.public.sqlserver.client' but got no
reply.
> > Any help with this problem would be greatly appreciated
> > -------
> > I developed a database under SQL Server 2000, with Access 2000 on
> > Windows 2000 as client. This had been running fine for several years.
> > The client is now upgrading to Windows XP, and has come across a display
> > problem on the reports. The figures are correct but are now displayed
> > with lots of trailing zeros e.g
> > 365.00000
> > I have built a test system but cannot duplicate the problem :-( . Also
> > the problem varies between computers, and even between different users
> > on the same computer. One solution that seems to work is to explicitly
> > cast all output from the server e.g.
> I have to admit that I saw your post in .clients, but I let it pass, since
> 1) I don't know Access, 2) There seems to be information missing from
> your posting.
> What data type are the columns in SQL Server? How does the code look
> like in Access to bring the data from SQL Server to the output? Do you
> just associate a record set with a window control, or is there some
> processing on the way?
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||David Greenwood (david at greenwood dot lu) writes:
> We use decimal fields with fixed three decimal places to store tonnes
> and kilos. Once these have been totalled, averaged etc they
> automatically track 6 decimal places of precision. After ROUNDing to 0
> places the data is returned to the client. However, running tests under
> Query Analyser shows that the 6 zeros are still part of the decimal
> answer. Windows 2000 has never had a problem with this, no matter what
> the language settings. The data has displayed okay. In contrast,
> Windows XP chokes on the data. I guess not many people use the decimal
> data type, so this has not been an issue.

You still don't tell us what you are doing, so I have to guess that you
are doing something like:

declare @.d decimal(18, 6)
select @.d = 21
select round(@.d, 0)

This displays as 21.000000 everywhere I tried, and this appears correct to
me. The return value of round() is the same as the input value.

I don't think Windows XP vs. 2000 has anything to do it. I tried it on
two Windows 2000 machines, and I got 21.00000 back on both. Rather I
would guess that it depends on the MDAC version. Windows XP comes with
MDAC 2.7, whereas Windows 2000 comes with MDAC 2.5 or even earlier, but
to get full use of SQL 2000, it's good to have MDAC 2.6 at least.

Interesting enough, I ran the above from ISQL, which uses DB-Library,
which is not part of the MDAC, and basically unchanged since 1997. It,
too, displays 21.000000.

So it seems that your code took a shortcut, depending of what actually is a
bug in an earlier versions on the MDAC.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp