MySQL Create and Select

[Guest gagan]

I was trying to solve some MySQL query questions .. Just need some feedback on the approach ..if its correct or not...1. We have a database table "txt_messages" containing an entry for each mobile text (SMS) message received by our system. Its data includes: - 10-digit US phone number of message sender - name of sender's cellular network (e.g. "cingular", "verizon") - date and time of receipt of message - first word of text message (the "keyword") a. What SQL statement(s) would you give to MySQL to create a table containing this data?create table txt_messages ( id INT(10) NOT NULL AUTO_INCREMENT, sendertel BIGINT(10) NOT NULL, network_name varchar(20), msg_recd DATETIME, keyword varchar(20), primary key (id) ); b. The first six digits of a US phone number is called the "NPA-NXX"; for example, the number "6266745901" has an NPA-NXX of "626674". Please provide a MySQL SELECT statement giving the NPA-NXX values present in the txt_messages table and the total number of messages received for each NPA-NXX between November 15, 2007 and November 30, 2007. mysql> select * from txt_messages;+----+------------+--------------+---------------------+-----------+| id | sendertel | network_name | msg_recd | keyword |+----+------------+--------------+---------------------+-----------+| 1 | 6092381579 | TMobile | 2007-12-06 08:51:06 | Hello || 2 | 6092319560 | TMobile | 2007-12-06 08:51:36 | Hello || 3 | 6191112222 | Cingular | 2007-12-06 08:53:31 | Come || 4 | 6192221111 | Cingular | 2007-12-06 08:53:50 | Go || 5 | 8180002323 | Verizon | 2007-11-15 09:08:36 | Foo || 6 | 8180004444 | Verizon | 2007-11-20 09:10:58 | Foo2 || 7 | 8180005555 | Verizon | 2007-11-20 09:11:41 | Foo23 || 8 | 8180005555 | Verizon | 2007-11-22 09:27:26 | Foo24 || 9 | 8180004444 | Verizon | 2007-11-22 09:28:09 | Foo3 |+----+------------+--------------+---------------------+-----------+9 rows in set (0.00 sec)select sendertel, SUBSTR(sendertel,1,6), COUNT(sendertel) from txt_messages where msg_recd between "2007-11-15" and "2007-11-30" GROUP BY sendertel;+------------+-----------------------+------------------+| sendertel | SUBSTR(sendertel,1,6) | COUNT(sendertel) |+------------+-----------------------+------------------+| 8180002323 | 818000 | 1 || 8180004444 | 818000 | 2 || 8180005555 | 818000 | 2 |+------------+-----------------------+------------------+ c. Enhance the SELECT statement of question b to also return the percentage of messages received between those dates with the given NPA-NXX.I need help on this query ...... I cant even understand it ..... :|

[justsomeguy]

The second query is grouping by the full number, not just the first 6 digits. Try this:select SUBSTR(sendertel,1,6) AS npanxx, COUNT(sendertel) from txt_messages where msg_recd between "2007-11-15" and "2007-11-30" GROUP BY npanxx;For the percentage, you can use math operators to divide the count of messages for the npanxx by the total number of messages. I don't want to write that query though, because this sounds like a school assignment. We can help you figure it out, but we try to stay away from giving the answers to assignments.