LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 09-13-2012, 07:13 PM   #1
vxrcorsa90
LQ Newbie
 
Registered: Sep 2012
Posts: 7

Rep: Reputation: Disabled
MySQL - Help !!


Hello,

can anyone help me make sense of this please :


select count(*) from csa where Customer like "%" and Agent in ("agent_user");

Ive managed to extract a report and then import it into mysql

I need to work out, how many chats an agent has taken... could somebody assist
 
Old 09-13-2012, 08:40 PM   #2
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Rocky 9.2
Posts: 18,344

Rep: Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746
Not quite sure what your qn is, but have a read of this https://dev.mysql.com/doc/refman/5.1...#operator_like

I recommend starting each keyword/operator on a separate line thus
Code:
select count(*) 
from csa 
where Customer like "%" 
and Agent in ("agent_user");
comments:

1. count(*) counts every col in every row that matches, although it does give you the row cnt at the end.
more efficient is 'count(1)' .

2. 'like "%"' as per that link, that's a completely wildcard match, so pointless

3. 'in ("agent_user")' : only use/need 'in' for a list of potential matches, otherwise wasting processor.
In this case
' = "agent_user" '
is better

HTH
Have a good read of the MySQL SQL docs; they're good & have lots of examples.
eg https://dev.mysql.com/doc/refman/5.1/en/functions.html
 
Old 09-14-2012, 01:12 AM   #3
vxrcorsa90
LQ Newbie
 
Registered: Sep 2012
Posts: 7

Original Poster
Rep: Reputation: Disabled
I should have been more clearer, i imported this CSV file :


171767567,(agent_name)
171767681,(agent_name)
171770100,(agent_name)
171770316,(agent_name)


created 2 columns called Account and User.

The account = INT
User = VARCHAR value of 20.

Now what i want to find out how many chats a given agent has taken based off the Account.. running this :


select count(*) from csa_totals where Account="1%" and User="agent_name";


now i want to see how many chats this particular agent_name has taken based off the wildcard... but it keeps returning as 0... even though the data is there... is there anything im doing wrong. ??
 
Old 09-14-2012, 05:57 AM   #4
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Rocky 9.2
Posts: 18,344

Rep: Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746
Lets start from the top
Code:
show create table csa_totals;

select *
from csa_totals
limit 10;
 
Old 09-14-2012, 06:30 AM   #5
vxrcorsa90
LQ Newbie
 
Registered: Sep 2012
Posts: 7

Original Poster
Rep: Reputation: Disabled
ok :

| Table | Create Table |
+------------+---------------------------------------------------------------------------------------------------------------------------------+
| csa_totals | CREATE TABLE `csa_totals` (
`Account` int(100) NOT NULL,
`User` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+---------------------------------------




+-----------+------------------+
| Account | User |
+-----------+------------------+
| 171767567 | (michael.gairo) |
| 171767681 | (broo4940) |
| 171770100 | (broo4940) |
| 171770316 | (jon4667) |
+-----------+------------------+
4 rows in set (0.12 sec)
 
Old 09-14-2012, 07:30 AM   #6
sundialsvcs
LQ Guru
 
Registered: Feb 2004
Location: SE Tennessee, USA
Distribution: Gentoo, LFS
Posts: 10,599
Blog Entries: 4

Rep: Reputation: 3905Reputation: 3905Reputation: 3905Reputation: 3905Reputation: 3905Reputation: 3905Reputation: 3905Reputation: 3905Reputation: 3905Reputation: 3905Reputation: 3905
If the data calls it "agent_name," then so should you. Anyhow, here is your answer:


SELECT
account,
user,
COUNT(*) AS account_user_num_conversations
FROM csa_totals
GROUP BY
account,
user
;


Time to get a good book on SQL and start reading.

You can waste an extraordinary amount of time "shooting in the dark" if you haven't received (or provided for yourself) the proper training. If your manager doesn't realize that there's a knowledge-deficiency among his or her ranks, it's okay to be the one to tell him or her. No one gets this stuff "by osmosis."
 
Old 09-14-2012, 08:57 AM   #7
vxrcorsa90
LQ Newbie
 
Registered: Sep 2012
Posts: 7

Original Poster
Rep: Reputation: Disabled
Thanks for the tip and your response it is much appreciated. One more question. Is it possible, to for example only query specific users, and see how many chats they took and return that. Because sometimes the Account may not start with 1, for example...?

Again i appreciate the help, and the reason why im taking this over, is because our main DBA is away ill, and im the next IT resource altho my speciality is Linux, Apache and Windows etc.
 
Old 09-16-2012, 02:44 PM   #8
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Rocky 9.2
Posts: 18,344

Rep: Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746Reputation: 2746
We can't teach you SQL by example; it would take mths. You need to read up on the MySQL docs as linked above.
Note that normally col names are case sensitive, eg account != Account.
Also, you seem to have created usernames that actually have brackets '(' , ')' as part of the name.
That's almost definitely wrong and will cause problems because they are meta-chars used in SQL, as in other programming langs.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Can MySQL log on via SSH/bash? mysql:x:27:101:MySQL Server:/var/lib/mysql:/bin/bash Ujjain Linux - Newbie 2 04-24-2009 02:21 PM
yum install php-mysql fails with mysql 5.1 - "Error: mysql conflicts with MySQL" rebelde Linux - Software 2 03-13-2009 10:32 AM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. SpellChainz Linux - Newbie 1 06-23-2007 03:35 PM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 08:44 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 02:33 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration