COUNT many but display just once?

Hi, I have a table that I insert a member's country into every time
someone signs up. What I'd like to do is pull information from the DB
such that I can see each country and the number of users from each.
For example:

Argentina 10
Brazil 5
Canada 3

I'm having trouble writing the SQL for this...any suggestions?

Thanks,

Erik
"Erik Lautier" <lautier@.gmail.comwrote in message
news:1177283677.045638.109480@.o5g2000hsb.googlegro ups.com...

Quote:

Originally Posted by

Hi, I have a table that I insert a member's country into every time
someone signs up. What I'd like to do is pull information from the DB
such that I can see each country and the number of users from each.
For example:
>
Argentina 10
Brazil 5
Canada 3

W/o schema it's impossible to say what you want for sure but something like

create table membership
(
country varchar(20),
signup varchar(20)
);

insert into membership values ('Argentina', 'member 1')
insert into membership values ('Argentina', 'member 2')
insert into membership values ('Brazil', 'member 1')
insert into membership values ('Brazil', 'member 1')
insert into membership values ('Brazil', 'member 2')
insert into membership values ('Brazil', 'member 3')
insert into membership values ('Canada', 'member 1')

select country, count(signup) from membership group by country

SELECT COUNTRY, COUNT(signup) from membership group by country

Quote:

Originally Posted by

>
I'm having trouble writing the SQL for this...any suggestions?
>
Thanks,
>
Erik
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That did the trick. Thanks!

On Apr 22, 8:23 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

"Erik Lautier" <laut...@.gmail.comwrote in message
>
news:1177283677.045638.109480@.o5g2000hsb.googlegro ups.com...
>

Quote:

Originally Posted by

Hi, I have a table that I insert a member's country into every time
someone signs up. What I'd like to do is pull information from the DB
such that I can see each country and the number of users from each.
For example:

>

Quote:

Originally Posted by

Argentina 10
Brazil 5
Canada 3

>
W/o schema it's impossible to say what you want for sure but something like
>
create table membership
(
country varchar(20),
signup varchar(20)
);
>
insert into membership values ('Argentina', 'member 1')
insert into membership values ('Argentina', 'member 2')
insert into membership values ('Brazil', 'member 1')
insert into membership values ('Brazil', 'member 1')
insert into membership values ('Brazil', 'member 2')
insert into membership values ('Brazil', 'member 3')
insert into membership values ('Canada', 'member 1')
>
select country, count(signup) from membership group by country
>
SELECT COUNTRY, COUNT(signup) from membership group by country
>
>
>

Quote:

Originally Posted by

I'm having trouble writing the SQL for this...any suggestions?

>

Quote:

Originally Posted by

Thanks,

>

Quote:

Originally Posted by

Erik

>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Could Publisher Server Pull Subscriber Server

Hello,
I have two SQL SERVERs and I would like to establish a simple architecture
of one Publisher and one SUbscriber. the catch is that I would like to use
SQL 2005 EXPRESS as my source DB - Production Server (even though, as far as
I understand, it can only be a Subscriber) and SQL SERVER 2005 STANDARD will
be the replica DB (even though it will probebly have to be Publisher).
So, is it pusible that SQL SERVER STANDARD, which acts as a Publisher, be
able to make a Pull operation (and it will be replica DB) from SQL SERVER
EXPRESS, which acts as a Subscriber (and it will be the source DB)?
It is important to note that at the moment the SQL SERVER EXPRESS is used as
the only SERVER we have and it is operational.
I would like to add SQL SERVER 2005 STANDARD in order to let it pull every 5
minutes (or so) the SQL SERVER 2005 EXPRESS and copy all the changes made in
the existing SQL 2005 EXPRESS operational server.
I do not have the option to shutdown the SQL 2005 EXPRESS SERVER in order to
upgrade it to a STANDARD vertion.
Thanks in advanced
Yossi
You can use merge replication for this. However your SQL Server Standard
will have to be the publisher which can push to the Express version.
Why do you need a pull on the SQL Server standard machine?
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"yoffir" <yoffir@.discussions.microsoft.com> wrote in message
news:5A0216BC-5D31-4AF2-B6BE-3A1B266C9A75@.microsoft.com...
> Hello,
> I have two SQL SERVERs and I would like to establish a simple architecture
> of one Publisher and one SUbscriber. the catch is that I would like to use
> SQL 2005 EXPRESS as my source DB - Production Server (even though, as far
> as
> I understand, it can only be a Subscriber) and SQL SERVER 2005 STANDARD
> will
> be the replica DB (even though it will probebly have to be Publisher).
> So, is it pusible that SQL SERVER STANDARD, which acts as a Publisher, be
> able to make a Pull operation (and it will be replica DB) from SQL SERVER
> EXPRESS, which acts as a Subscriber (and it will be the source DB)?
> It is important to note that at the moment the SQL SERVER EXPRESS is used
> as
> the only SERVER we have and it is operational.
> I would like to add SQL SERVER 2005 STANDARD in order to let it pull every
> 5
> minutes (or so) the SQL SERVER 2005 EXPRESS and copy all the changes made
> in
> the existing SQL 2005 EXPRESS operational server.
> I do not have the option to shutdown the SQL 2005 EXPRESS SERVER in order
> to
> upgrade it to a STANDARD vertion.
> Thanks in advanced
> Yossi
>
|||I want to save the huge fee fore having a license for the STANDARD version.
The database is used by many users and a license will give Bill Gats 7000$
extra to his bank account.
Currently we are using the EXPRESS version free of charge.
I would like to have a replica of that database in a remote site.
The thing is that it is pusible that our programmers will use the replica
database and make changes to it. So, the original EXPRESS database must not
be updated by the replica database.
This is why I thought to use a publisher STANDARD database as a replica and
pull the subscriber EXPRESS database every 5 minutes for changes.
If I will use a publisher STANDARD server I will have to pay only 800$ for a
single user license (sa).
Hope I didn't confuse you
"Hilary Cotter" wrote:

> You can use merge replication for this. However your SQL Server Standard
> will have to be the publisher which can push to the Express version.
> Why do you need a pull on the SQL Server standard machine?
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "yoffir" <yoffir@.discussions.microsoft.com> wrote in message
> news:5A0216BC-5D31-4AF2-B6BE-3A1B266C9A75@.microsoft.com...
>
>

could not see the replication db under publication

Hello,
I am trying to setup a pull subscription through wizard.
I have a problem when I choose the publicaiton to which I
wnat to subscribe. I can see the publication server,
however, there is nothing under it when I expand the
publisher. Is that anyone knows what it is worng of it.
Any sugessation or help would be really appreciate.
Thanks,
On the Publisher, make sure that the Subscriber is enabled.
"ping" <ping.li@.nokia.com> wrote in message
news:1a9601c5421b$a6bc36b0$a601280a@.phx.gbl...
> Hello,
> I am trying to setup a pull subscription through wizard.
> I have a problem when I choose the publicaiton to which I
> wnat to subscribe. I can see the publication server,
> however, there is nothing under it when I expand the
> publisher. Is that anyone knows what it is worng of it.
> Any sugessation or help would be really appreciate.
> Thanks,
>

Could not run the pull subscription

Indra,
try changing the owner of the job to sa.
HTH,
Paul Ibison
Hi paul, Do i have to run sql agent and sql server in boht computer as a
sa, it runing now but just for information.
Can i ask you some more informatio.
As I am going to install MSDE to our field user to whome i wanted to
give database so that they can work offline and when they want to
synchronize on demand. How i have to create the security premision, what
type to user account, window/sql mix or only window or only sql and what
type of owner ship they need so that they can replicate or synchronize
the data with our center server. Could you please give me some
information or the link where from i can read a bit.
Thanks a lot.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Indra,
in BOL have a look at replication, security. The subsections are agent login
security and Publication Access Lists. Your SQL Server Agent is the context
under which the replication jobs function. Typically the replication agent
is set to impersonate its login on the server it connects to, so if you want
a really simple setup, have the same domain user as the service user for sql
server and the agent services on your publisher/distributer and the
subscriber. If you want it to be more granular then the references in BOL
above will help.
The reason I mentioned setting the job's owner to sa is that although the
job runs as the agent, the replication process involves a verification that
the job owner is in active directory and setting the owner to sa avoids this
issue.
SQL Logins in replication are usually used for non-trusted domains.
HTH,
Paul Ibison
|||Thanks a lot Paul,
This is really a good information. I wanted to go for the simplest way
so that there will be no problem from the client site. Write now i am
testing as you mention with giving the dbowner permition, is it
necessary to give that permision or just data reader or data writer also
work in the case of repliction.
I need little bit more information, if you can help me. Acutally we are
using ACCESS database asd i am on the process to migrate (doing all the
testing, before actual migration).
I already migrated (for test) to sql database keeping front end as it is
ADP file. What i want to do is copy all ADP file to client m/c and
install local MSDE AND SYNCHRONIZE THROUGH MERGE REPLICATION ON DEMAND,
SO THAT USER CAN ALSO WORK WHERE THERE IN NO INTERNET ACCESS. TILL NOW
I have just converted to sql and trying to run merge on the one client,
but not yet tested for ADP file with new replicated data. Do you have
any idea or information regarding this type of project.
Thank you very much for help.
Indra.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Indra,
For security, the role requirements are different for each agent. Have a
look in BOL for replication, security, Role Requirements.
For Access-SQL replication, have a look in BOL for these articles:
"Implementing Merge Replication to Access Subscribers"
"Data type mapping to Jet-SQL 4.0 for Merge Replication"
HTH,
Paul Ibison

Could not find stored procedure 'sp_MSupd_xxxxxx'

I created a pull subscription to pull information from a production
database. When I started the subscription, the error "Could not find stored
procedure 'sp_MSupd_tablename'" showed.
I deleted the subscription in the subscriber and drop the distribution by
running the "Disable Publishing and Distribution Wizard" in the distributor.
Then, I recreated both the distribution and subscription from the very
beginning once again. However, the same error still showed.
I'd be grateful is anybody can help.
Thanks in advance!
KM
If you have used @.sync_type = None in sp_addsubscription or in the
subscription wizard, 'Initialize subscription' page, you select 'no, the
subscriber already has the schema and data' you may get this error. The
system tables for replication will be created on the subscriber in either
case but you'll need to manually create the stored procedures on the
subscriber if you use this option otherwise you'll have the above error when
you change a row on the publisher. Use sp_scriptpublicationcustomprocs
(assuming you have >= sp1) to create a script which creates the procedures
and run this script on the subscriber.
HTH,
Paul Ibison
"krygim" <krygim@.hotmail.com> wrote in message
news:ewJovUqhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> I created a pull subscription to pull information from a production
> database. When I started the subscription, the error "Could not find
stored
> procedure 'sp_MSupd_tablename'" showed.
>
> I deleted the subscription in the subscriber and drop the distribution by
> running the "Disable Publishing and Distribution Wizard" in the
distributor.
> Then, I recreated both the distribution and subscription from the very
> beginning once again. However, the same error still showed.
>
> I'd be grateful is anybody can help.
>
>
> Thanks in advance!
>
> KM
>
|||Hi Paul,
Thanks a lot for your information. However, I was not able to find
sp_scriptpublicationcustomprocs in BOL. Could you show me how to use of the
procedure?
Thanks in advance.
KM
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e4pQoWrhEHA.1184@.TK2MSFTNGP12.phx.gbl...
> If you have used @.sync_type = None in sp_addsubscription or in the
> subscription wizard, 'Initialize subscription' page, you select 'no, the
> subscriber already has the schema and data' you may get this error. The
> system tables for replication will be created on the subscriber in either
> case but you'll need to manually create the stored procedures on the
> subscriber if you use this option otherwise you'll have the above error
when[vbcol=seagreen]
> you change a row on the publisher. Use sp_scriptpublicationcustomprocs
> (assuming you have >= sp1) to create a script which creates the procedures
> and run this script on the subscriber.
> HTH,
> Paul Ibison
>
> "krygim" <krygim@.hotmail.com> wrote in message
> news:ewJovUqhEHA.1356@.TK2MSFTNGP09.phx.gbl...
> stored
by
> distributor.
>
|||This scenario may not be exactly yours, but you'll get the
idea:
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;320499

>--Original Message--
>Hi Paul,
>Thanks a lot for your information. However, I was not
able to find
>sp_scriptpublicationcustomprocs in BOL. Could you show me
how to use of the[vbcol=seagreen]
>procedure?
>Thanks in advance.
>KM
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:e4pQoWrhEHA.1184@.TK2MSFTNGP12.phx.gbl...
sp_addsubscription or in the[vbcol=seagreen]
you select 'no, the[vbcol=seagreen]
this error. The[vbcol=seagreen]
subscriber in either[vbcol=seagreen]
procedures on the[vbcol=seagreen]
the above error[vbcol=seagreen]
>when
sp_scriptpublicationcustomprocs[vbcol=seagreen]
creates the procedures[vbcol=seagreen]
from a production[vbcol=seagreen]
error "Could not find[vbcol=seagreen]
the distribution[vbcol=seagreen]
>by
Wizard" in the[vbcol=seagreen]
subscription from the very[vbcol=seagreen]
showed.
>
>.
>
|||Thanks
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:2c3f01c486c2$b82bc470$a301280a@.phx.gbl...[vbcol=seagreen]
> This scenario may not be exactly yours, but you'll get the
> idea:
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;320499
>
> able to find
> how to use of the
> sp_addsubscription or in the
> you select 'no, the
> this error. The
> subscriber in either
> procedures on the
> the above error
> sp_scriptpublicationcustomprocs
> creates the procedures
> from a production
> error "Could not find
> the distribution
> Wizard" in the
> subscription from the very
> showed.
|||Later editions of BOL may have these details, but if not this link has more
details: http://support.microsoft.com/default...b;EN-US;299903
Regards,
Paul Ibison
|||Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uOnbmgthEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Later editions of BOL may have these details, but if not this link has
more
> details: http://support.microsoft.com/default...b;EN-US;299903
> Regards,
> Paul Ibison
>
|||Hi
do let me know if you've had any difficulties running
"sp_scriptpublicationcustomprocs".
After creating the replication as described in the KB 320499, if you see
that Inserts get replicated well but "updates" gives
us the Following error;
"37000 Cannot update identity column 'column1'. 8102 "
Do let me know.
SB.