difficult query...for me
Franz
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
Captain Paralytic
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 by:put the COUNT of visits for each "idclub"?
Robert Hairgrove
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).
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 by:put 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
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).
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
Robert Hairgrove
> 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.
Franz
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
Jerry Stuckle
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.
jstu...@attglobal.net
==================
Robert Hairgrove
> 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;
Captain Paralytic
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
> 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.
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?
Robert Hairgrove
> 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
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
> 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.
Jerry Stuckle
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.
Richard
"Jerry Stuckle" <jstu...@attglobal.net> wrote in message
news:hemqau$m4c$1...@news.eternal-september.org...
there are all kinds of reasons why any database could be set up a
certain way.
Without knowing the specific situation, I think there is no way to
judge if this is "good" or "bad".
Dont judge too soon here, we know nothing of the guys situation.
I can think of valid reasons to store a sum, historical reports is
one.
Easy to shout "booboo" from a distance...
Competent programmers / designers ask questions, until there is
nothing more to ask.
Thats not what you guys are doing here.
R.
Jerry Stuckle
There are things to look to avoid in ANY database design. This is one
of them.
> Dont judge too soon here, we know nothing of the guys situation.
>
Spoken like someone who's looking for excuses for poor design.
> I can think of valid reasons to store a sum, historical reports is
> one.
>
That is not the reason here, though, is it. But in any case, that's
still not a reason to store a sum.
> Easy to shout "booboo" from a distance...
>
> Competent programmers / designers ask questions, until there is
> nothing more to ask.
> Thats not what you guys are doing here.
>
> R.
>
>
Yep, it's easy for incompetent idiots to shout from a distance. Now,
why don't you just crawl back in your hole where you belong and leave
the design to those who know what they're doing?
Richard
I love you too, Jerry.
Yours,
R.
Erick T. Barkhuis
>Richard wrote:
>>"Jerry Stuckle" <jstu...@attglobal.net> wrote in message
>>> 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.
>>Easy to shout "booboo" from a distance...
>>
>>Competent programmers / designers ask questions, until there is
>>nothing more to ask. Thats not what you guys are doing here.
>
>Yep, it's easy for incompetent idiots to shout from a distance. Now,
>why don't you just crawl back in your hole where you belong and leave
>the design to those who know what they're doing?
Darn, Jerry!
Is it really necessary to go in this direction with every second thread?
Certainly, many technical experts know a lot, but at the same time have
difficulties communicating 'nicely'. However, it isn't absolutely
required to make it so obvious who qualifies for this group.
--
Erick
Franz
> Dont judge too soon here, we know nothing of the guys situation.
>
> I can think of valid reasons to store a sum, historical reports is
> one.
>
> Easy to shout "booboo" from a distance...
>
historical report IS the reason I was asking suggestions for.
Thanks for your partecipation.
Franz
Erick T. Barkhuis
>Richard ha scritto:
>
>>Dont judge too soon here, we know nothing of the guys situation.
>>
>>I can think of valid reasons to store a sum, historical reports is
>>one.
>
>historical report IS the reason I was asking suggestions for.
Still, I wonder if this is really necessary for historical reports,
UNLESS you also remove historical data.
In case you always have the required data in your database, a
historical report could simply be produced using something like
SELECT a,b,c FROM table
WHERE dateA > "2007-01-00" AND dateA < "2008-12-99"
However, in case you remove data from the database and you want reports
afterwards, anyway, data storage and perhaps storage of aggregated
results in a (small type of) data warehouse would be a solution. That
would enable the customer to produce all types of reports without
keeping the original data in his live database.
From your original question, I don't get the impression that this would
be a required step, though.
--
Erick
Jerry Stuckle
So a second troll shows up. I respond to trolls in the only language
they understand.
Jerry Stuckle
Still completely unnecessary - and does not create a historical report.