difficult query...for me

Hi everybody,
I cannot figure out how to resolve this query that I am gonna describe
to you:

I have 2 tables:
CLUB
VISITS

CLUB contains many fields but the affected ones are:
idclub
number_of_visits

VISITS contains:
idvisit
type_of_visit
idclub

What I would like the query to do is:

list the CLUB table and in the "number_of_visit" field and put the COUNT
of visits for each "idclub" simply counting the rows of VISITS where
visits.idclub=club.idclub

Any suggestion about how to do it?

I did something like counting in a nested select...but i messed
everything up.

Thanks

Franz

On 26 Nov, 11:20, Franz <f...@despammed.com> wrote:
> Hi everybody,
> I cannot figure out how to resolve this query that I am gonna describe
> to you:
>
> I have 2 tables:
> CLUB
> VISITS
>
> CLUB contains many fields but the affected ones are:
> idclub
> number_of_visits
>
> VISITS contains:
> idvisit
> type_of_visit
> idclub
>
> What I would like the query to do is:
>
> list the CLUB table and in the "number_of_visit" field and put the COUNT
> of visits for each "idclub" simply counting the rows of VISITS where
> visits.idclub=club.idclub
>
> Any suggestion about how to do it?
>
> I did something like counting in a nested select...but i messed
> everything up.
>
> Thanks
>
> Franz

If you have a VISITS table which contains 1 record for every visit,
then to find the number of visits for a club you just need to do a
COUNT & GROUP BY, whenever you want to find this information.

But what do you mean byut the COUNT of visits for each "idclub"?

Franz wrote:
> Hi everybody,
> I cannot figure out how to resolve this query that I am gonna describe
> to you:
>
> I have 2 tables:
> CLUB
> VISITS
>
> CLUB contains many fields but the affected ones are:
> idclub
> number_of_visits
>
> VISITS contains:
> idvisit
> type_of_visit
> idclub
>
> What I would like the query to do is:
>
> list the CLUB table and in the "number_of_visit" field and put the COUNT
> of visits for each "idclub" simply counting the rows of VISITS where
> visits.idclub=club.idclub
>
> Any suggestion about how to do it?
>
> I did something like counting in a nested select...but i messed
> everything up.
>
> Thanks
>
> Franz

It's not a good idea to store aggregate data (i.e. count of something)
in a column because every time a new visit occurs, you must update the
column. Data rapidly becomes inconsistent. You can always create a view
which shows you the data the way you describe (i.e. count of visits
together with the club id).

Someone else already pointed you in the right direction (hint: look up
"GROUP BY" in the SQL syntax part of the documentation).

Captain Paralytic ha scritto:

>
> If you have a VISITS table which contains 1 record for every visit,
> then to find the number of visits for a club you just need to do a
> COUNT & GROUP BY, whenever you want to find this information.
>
> But what do you mean byut the COUNT of visits for each "idclub"?

Hello Captain

I mistyped something
I meant:
list the CLUB table and put the COUNT of visits for each "idclub" simply
counting the rows of VISITS where visits.idclub=club.idclub in the
"number_of_visit" field

Franz

Robert Hairgrove ha scritto:

> It's not a good idea to store aggregate data (i.e. count of something)
> in a column because every time a new visit occurs, you must update the
> column. Data rapidly becomes inconsistent. You can always create a view
> which shows you the data the way you describe (i.e. count of visits
> together with the club id).
>
> Someone else already pointed you in the right direction (hint: look up
> "GROUP BY" in the SQL syntax part of the documentation).

thank you Robert,
I know how to group by with SQL, but this is not the case.
The visits I am talking about are not clicks on a web link, so the data
aren't so rapid

They're manual insertions of dates and other datas.

Any idea about how to fill the "number_of_visit" field as requested in
my previous post?

Thanks

Franz

On 26 Nov, 12:25, Franz <f...@despammed.com> wrote:
> Robert Hairgrove ha scritto:
>
> > It's not a good idea to store aggregate data (i.e. count of something)
> > in a column because every time a new visit occurs, you must update the
> > column. Data rapidly becomes inconsistent. You can always create a view
> > which shows you the data the way you describe (i.e. count of visits
> > together with the club id).
>
> > Someone else already pointed you in the right direction (hint: look up
> > "GROUP BY" in the SQL syntax part of the documentation).
>
> thank you Robert,
> I know how to group by with SQL, but this is not the case.
> The visits I am talking about are not clicks on a web link, so the data
> aren't so rapid
>
> They're manual insertions of dates and other datas.
>
> Any idea about how to fill the "number_of_visit" field as requested in
> my previous post?
>
> Thanks
>
> Franz

The answers are still the same. It is still not a good idea and if you
really want to do it against all advice, the answer is still using
COUNT and GROUP BY.
Since you say that you "know how to group by with SQL", then what is
it that you do not know how to do in order to implement this bad idea?

Franz wrote:
> Any idea about how to fill the "number_of_visit" field as requested in
> my previous post?

UPDATE club C SET C.number_of_visits = (SELECT count(*) FROM visits V
WHERE V.idclub = C.idclub);

But it is not a good idea, as I stated in my first post, because the
actual number of visits can change in the meantime, and someone might
see old data.

Robert Hairgrove ha scritto:
> Franz wrote:
>> Any idea about how to fill the "number_of_visit" field as requested in
>> my previous post?
>
> UPDATE club C SET C.number_of_visits = (SELECT count(*) FROM visits V
> WHERE V.idclub = C.idclub);
>
> But it is not a good idea, as I stated in my first post, because the
> actual number of visits can change in the meantime, and someone might
> see old data.

I'm gonna try your solution, even if i cannot use the UPDATE statement.
I should use SELECT to list the resulting rows.
By the way, nobody can see these datas as only the admin has right to
list, change and add records to these tables.
So, if admin won't change anything, datas are persistent during all the
time elapsed between 2 insertions.

Thank you robert

Franz

Franz wrote:
> Robert Hairgrove ha scritto:
>> Franz wrote:
>>> Any idea about how to fill the "number_of_visit" field as requested
>>> in my previous post?
>>
>> UPDATE club C SET C.number_of_visits = (SELECT count(*) FROM visits V
>> WHERE V.idclub = C.idclub);
>>
>> But it is not a good idea, as I stated in my first post, because the
>> actual number of visits can change in the meantime, and someone might
>> see old data.
>
> I'm gonna try your solution, even if i cannot use the UPDATE statement.
> I should use SELECT to list the resulting rows.
> By the way, nobody can see these datas as only the admin has right to
> list, change and add records to these tables.
> So, if admin won't change anything, datas are persistent during all the
> time elapsed between 2 insertions.
>
> Thank you robert
>
> Franz

I agree with Robert. You should not be storing aggregate data in your
database. It has nothing to do with how rapidly the data changes or how
the data is input. It's all about maintaining consistency in your database.

As others have said - you can easily get the number of visits by using
COUNT and GROUP BY.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 26 Nov, 13:04, Franz <f...@despammed.com> wrote:
> Robert Hairgrove ha scritto:
>
> > Franz wrote:
> >> Any idea about how to fill the "number_of_visit" field as requested in
> >> my previous post?
>
> > UPDATE club C SET C.number_of_visits = (SELECT count(*) FROM visits V
> > WHERE V.idclub = C.idclub);
>
> > But it is not a good idea, as I stated in my first post, because the
> > actual number of visits can change in the meantime, and someone might
> > see old data.
>
> I'm gonna try your solution, even if i cannot use the UPDATE statement.
> I should use SELECT to list the resulting rows.
This doesn't seem to make sense??? His solution is all about using the
UPDATE statement. However the question is still WHY would you want
this number_of_visits field at all when it is not needed. Or is your
speciality bad daabase design?

> By the way, nobody can see these datas as only the admin has right to
> list, change and add records to these tables.
> So, if admin won't change anything, datas are persistent during all the
> time elapsed between 2 insertions.
And this matters why?

Franz wrote:
> I'm gonna try your solution, even if i cannot use the UPDATE statement.
> I should use SELECT to list the resulting rows.

If you cannot do updates, how are you going to "try my solution"?

Try this instead:

SELECT C.idclub, (select count(*) from visits V where V.idclub=C.idclub)
as `number_of_visits` FROM club C;

On 26 Nov, 13:30, Robert Hairgrove <rhairgr...@bigfoot.com> wrote:
> Franz wrote:
> > I'm gonna try your solution, even if i cannot use the UPDATE statement.
> > I should use SELECT to list the resulting rows.
>
> If you cannot do updates, how are you going to "try my solution"?
>
> Try this instead:
>
> SELECT C.idclub, (select count(*) from visits V where V.idclub=C.idclub)
> as `number_of_visits` FROM club C;

Isn't that all rather unnecessary?

SELECT
idclub,
COUNT(*)
FROM visits
GROUP BY idclub

Or, if you really want to include some data from the CLUB table
SELECT
c.idclub,
c.other_club_info,
COUNT(*)
FROM club c
JOIN visit v USING(idclub)

Captain Paralytic wrote:
> Isn't that all rather unnecessary?
>
> SELECT
> idclub,
> COUNT(*)
> FROM visits
> GROUP BY idclub
>
> Or, if you really want to include some data from the CLUB table
> SELECT
> c.idclub,
> c.other_club_info,
> COUNT(*)
> FROM club c
> JOIN visit v USING(idclub)

The first query won't give you any rows for clubs which haven't been
visited.

Captain Paralytic ha scritto:

> The answers are still the same. It is still not a good idea and if you
> really want to do it against all advice, the answer is still using
> COUNT and GROUP BY.
> Since you say that you "know how to group by with SQL", then what is
> it that you do not know how to do in order to implement this bad idea?

why beeing so caustic to me?
I have some knowledge about grouping in sql statements.
What I was asking for was a suggestion about how a field of a given
table can be filled with a sum coming from another table and bla bla bla..

Robert gave me a solution that is almost perfect.
That's all.
I don't know if the database I'm working on is bad designed. I cannot
change it.
It's not mine.
But I have to make some manipulations.
By the way, thank you too for your help. Next time I'll know how to NOT
design database.


Franz.

Franz wrote:
> Captain Paralytic ha scritto:
>
>> The answers are still the same. It is still not a good idea and if you
>> really want to do it against all advice, the answer is still using
>> COUNT and GROUP BY.
>> Since you say that you "know how to group by with SQL", then what is
>> it that you do not know how to do in order to implement this bad idea?
>
> why beeing so caustic to me?
> I have some knowledge about grouping in sql statements.
> What I was asking for was a suggestion about how a field of a given
> table can be filled with a sum coming from another table and bla bla bla..
>
> Robert gave me a solution that is almost perfect.
> That's all.
> I don't know if the database I'm working on is bad designed. I cannot
> change it.
> It's not mine.
> But I have to make some manipulations.
> By the way, thank you too for your help. Next time I'll know how to NOT
> design database.
>
>
> Franz.

You should know if it's designed well or not, and if it isn't, you
should be able to let your employer or client know, and why there are
problems. It's what competent programmers and designers do.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================