Re: counting question
Hello.
What do you think about this:
SELECT u.name,
IFNULL(s.skill_name,'user doesn\'t have any
skill') AS SKILL,
(
SELECT COUNT(skill_id)
FROM user_skill us1
WHERE us1.skill_id = us.skill_id
) AS COUNT
FROM user u
LEFT JOIN user_skill us ON u.id = us.user_id
LEFT JOIN skill s ON us.skill_id = s.id
ORDER BY u.name;
+--+-+---+
| name | SKILL | COUNT |
+--+-+---+
| u1 | s1 | 2 |
| u1 | s3 | 1 |
| u2 | user doesn't have any skill | 0 |
| u3 | s1 | 2 |
| u3 | s2 | 1 |
+--+-+---+
Dean Karres [EMAIL PROTECTED] wrote:
Hi,
I have three simple tables:
user
{
id int PRIMARY
name varchar UNIQUE
}
skill
{
id int PRIMARY
skill_name varchar UNIQUE
}
user_skill
{
user_id int
skill_id int
UNIQUE KEY id (user_id, skill_id)
}
Each user can have zero or more associated skills. So you might have
these associations:
User_1: Skill_1, Skill_3
User_2: no listed skills
User_3: Skill_2, Skill_1
Assuming that is all the users and skills, I would like to produce a
report that lists each user and then the list of skills each has (if
any). Along with each skill I want to print the count of how many times
this skill has been mapped to a user. Using the info above then
something like:
User_1
Skill_1 (2)
Skill_3 (1)
User_2
User_3
Skill_1 (2)
Skill_2 (1)
The skill usage counts are in parens. I get that there will prolly be
a join in order to pick up the fact that User_2 has no listed skills.
What I'm not sure about is how to get the over all skill counts. Do I
need to do the query that returns the user and skill list then go
through the per user skills and do additional queries to get the skill
counts?
help?
Dean...K...
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting question
Thanks to all that responded about my counting question. Gleb Paharenko's solution using the sub-select and the LEFT JOINS was the closest to what I needed. All the best, Dean...K... -- Dean Karres / karres at itg dot uiuc dot edu / www.itg.uiuc.edu Imaging Technology Group / Beckman Institute University of Illinois 405 North Mathews / Urbana, IL 61801 USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting question
Try this out:
select user.name, skill.skill_name, count(user_skill1.skill_id)
from user , skill, user_skill , user_skill as user_skill1
where user.id=user_skill.user_id and user_skill.skill_id=skill.id and
skill.id = user_skill1.skill_id
order by user.id.
I think this would work.
--
Parag
B. Tech
IIIT, Hybd
select
On 7/31/05, Dean Karres [EMAIL PROTECTED] wrote:
Hi,
I have three simple tables:
user
{
id int PRIMARY
name varchar UNIQUE
}
skill
{
id int PRIMARY
skill_name varchar UNIQUE
}
user_skill
{
user_id int
skill_id int
UNIQUE KEY id (user_id, skill_id)
}
Each user can have zero or more associated skills. So you might have
these associations:
User_1: Skill_1, Skill_3
User_2: no listed skills
User_3: Skill_2, Skill_1
Assuming that is all the users and skills, I would like to produce a
report that lists each user and then the list of skills each has (if
any). Along with each skill I want to print the count of how many times
this skill has been mapped to a user. Using the info above then
something like:
User_1
Skill_1 (2)
Skill_3 (1)
User_2
User_3
Skill_1 (2)
Skill_2 (1)
The skill usage counts are in parens. I get that there will prolly be
a join in order to pick up the fact that User_2 has no listed skills.
What I'm not sure about is how to get the over all skill counts. Do I
need to do the query that returns the user and skill list then go
through the per user skills and do additional queries to get the skill
counts?
help?
Dean...K...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
When the going gets tough only the tough gets going
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting question
I could not help to resist to create the tables and
try the hint, which is pretty close to the working
solution.
I just had to replace order by user.id with group
by user.name skill.skill_name
regards,
esv.
--- Parag Agrawal [EMAIL PROTECTED] wrote:
Try this out:
select user.name, skill.skill_name,
count(user_skill1.skill_id)
from user , skill, user_skill , user_skill as
user_skill1
where user.id=user_skill.user_id and
user_skill.skill_id=skill.id and
skill.id = user_skill1.skill_id
order by user.id.
I think this would work.
--
Parag
B. Tech
IIIT, Hybd
select
On 7/31/05, Dean Karres [EMAIL PROTECTED]
wrote:
Hi,
I have three simple tables:
user
{
id int PRIMARY
name varchar UNIQUE
}
skill
{
id int PRIMARY
skill_name varchar UNIQUE
}
user_skill
{
user_id int
skill_id int
UNIQUE KEY id (user_id, skill_id)
}
Each user can have zero or more associated skills.
So you might have
these associations:
User_1: Skill_1, Skill_3
User_2: no listed skills
User_3: Skill_2, Skill_1
Assuming that is all the users and skills, I would
like to produce a
report that lists each user and then the list of
skills each has (if
any). Along with each skill I want to print the
count of how many times
this skill has been mapped to a user. Using the
info above then
something like:
User_1
Skill_1 (2)
Skill_3 (1)
User_2
User_3
Skill_1 (2)
Skill_2 (1)
The skill usage counts are in parens. I get
that there will prolly be
a join in order to pick up the fact that User_2
has no listed skills.
What I'm not sure about is how to get the over all
skill counts. Do I
need to do the query that returns the user and
skill list then go
through the per user skills and do additional
queries to get the skill
counts?
help?
Dean...K...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
When the going gets tough only the tough gets going
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Enrique Sanchez Vela
email: [EMAIL PROTECTED]
-
It's often easier to fight for one's||We live in the outer space
principles than to live up to them||Rev. Kay Greenleaf
Adlai Stevenson||
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Counting question
Try this: select delivery, count(*) as ticketcount from ticketsales where delivery=post or delivery=pickup group by delivery Andy -Original Message- From: Ville Mattila [mailto:[EMAIL PROTECTED] Sent: 03 July 2003 11:28 To: [EMAIL PROTECTED] Subject: Counting question Hi there, We are currently having a large festival here in Ikaalinen, Finland (www.satahamesoi.fi) and we have our booking system based on MySQL. I should find out how many tickets are sold in each concert with different delivery methods. How could I make a count like this: SELECT COUNT(delivery='post') AS post, COUNT(delivery='pickup') AS pickup... Or should I just make two separated queries? Thanks, Ville -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
