"Joppe A" <[EMAIL PROTECTED]> wrote on 03/01/2005 02:21:31 PM:
> Hello all,
>
> I have I small problem I wonder if there is a easy solution for..
>
> I have a SQL-query where I count out how many users per "n_id" I
> have. it is a little tricky because I had to do a left joint and
> compare two tables to get out thoose that are represented only in
> one of the tables.
>
> SELECT SUBS.NE_ID, count(*) from SUBS left join SUB on SUBS.
> SC_ID=SUB.SC_ID WHERE SUB.SC_ID is NULL group by SUBS.NE_ID;
>
> The result I get out looks like this
>
> NE_ID count
> 01 10
> 02 11
> 03 4
> 04 8
>
> And now to my problem, I would like to count this together with
> another table I have instead of have a seperate query and count them
> together manually...
>
> SELECT count(*) from SUBS_DEAC where NE_ID=01
>
> I want to have these extra query added in my first query and get out
> the result as above (but now are SUBS and SUBS_DEAC counted together)
>
> I can also mention that my MySQL db is version 3.23.55. Unfortunatly
> is no update to newer version at the moment planed...
>
> Would be greatfull to get some help!
>
> /Joppe
>
You will have to count(distinct ) on your parent table in order to count
parents and children in the same query. Without knowing your particular
table structures I will make up an example to illustrate this type of
query
Here is one way to track users and messages in a chat room. One table
keeps track of when a user logs in and logs out. The other table tracks
messages (who to, who from, what time, and the message itself)
CREATE TABLE logins (
id bigint unsigned auto_increment not null primary key
, user_ID varchar(10) not null
, logintime datetime not null
, logouttime datetime null
);
CREATE TABLE messages (
id bigint unsigned auto_increment not null primary key
, login_id_from bigint unsigned not null
, login_id_to bigint unsigned not null
, msgcontent varchar(255) not null
, msgtime datetime not null
);
(note: creating a set of sample data is left as an exercise for the
reader)
To find out how many times a user has logged in, you could write a query
like this:
SELECT user_id, count(id) as login_count
FROM logins
GROUP BY user_id;
Pretty straightforward, right? Now how about how many messages are sent
from each login? That would also be a simple single-table count:
SELECT login_id_from, count(id) as message_count
FROM messages
GROUP BY login_ID;
Where things get tricky is if you want to know how many times someone has
logged in AND how many messages they have sent in the same query. Let's
start from a simpler query that returns just the user's activity. If I
were just going to pull a complete log of a user's activity, I would write
a query like this:
SELECT l.user_id
, l.logintime
, l.logouttime
, if(l.id=m.login_id_from,'S','R') as send_recv
, m.msgtime
, m.msgcontent
FROM logins l
INNER JOIN messages
on l.id IN (m.login_id_from, m.login_id_to)
WHERE user_id = 'jsmith'
ORDER BY m.msgtime;
I should get a list of every message the user 'jsmith' ever sent or
received, right? Assuming jsmith has logged in 3 times and sent or
received 300 messages total then the following statistics will apply to
the data I get back from that query
# of times the name 'jsmith' appears in the results = 300
# of times each logintime or logouttime is listed = once per message sent
during that login.
In numerical terms, if during login #1 he sent/received 50 messages, login
#2 he sent/received 150 messages, and login #3 he sent/received 100
messages, then those login and logout times will be repeated that many
times in our results. (50 copies of the logintime and logouttimes for
login #1, etc)
Because of those repeats we can't just write a query like the one below to
get a count of both logins and messages by user. Because each login is
listed right along with each message created during that login, the # of
values counted for both logins and messages will be identical for each
user.
SELECT l.user_id
, count(l.id) as login_count
, count(m.id) as message_count
FROM logins l
INNER JOIN messages m
ON l.id IN (m.login_id_from, m.login_id_to)
GROUP BY l.user_id;
What we want to do is count how many DIFFERENT logins have occurred not
just how many login values we have:
SELECT l.user_id
, count(DISTINCT l.id) as login_count
, count(m.id) as message_count
FROM logins l
INNER JOIN messages m
ON l.id IN (m.login_id_from, m.login_id_to)
GROUP BY l.user_id;
Now if we look at the line where user_id = 'jsmith', we should see the
statistics of 3 logins and 300 messages which is what we expect.
Hopefully you will be able to extend my example to match your original
problem. You will need to use COUNT(DISTINCT ) on one of your fields but
since you didn't post your table structures, I can't tell you which one.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
