0

Hello,

I was wondering if the following mysql query can be constructed in a much more efficient way?

SELECT distinct(cr.email),  m.fname, m.lname, m.email, (SELECT count(*) FROM  campaignRecipts where email=cr.email )as ttlsent,  (SELECT count(received) FROM  campaignRecipts where received='yes' and email=cr.email )as ttlreceived, ((SELECT count(received) FROM  campaignRecipts where received='yes' and email=cr.email )/(SELECT count(*) FROM  campaignRecipts where email=cr.email ))*100 as percentage  FROM  campaignRecipts as cr, maillist as m where cr.email=m.email group by ttlsent asc

Additionally, how could I round up the percentage --all within the query...

I appreciate any thoughts on this!

Best,

3
Contributors
11
Replies
79
Views
2 Years
Discussion Span
Last Post by mbarandao
0

All your sub-queries join campaignRecipts so it might be possible to get your results with a regular join and aggregates. Without some sample data it'll be hard to give a definitive answer.

0

good day pritaeas,

would the following sql dump data help?

--
-- Table structure for table `campaignRecipts`
--

DROP TABLE IF EXISTS `campaignRecipts`;
CREATE TABLE IF NOT EXISTS `campaignRecipts` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `campaignID` varchar(12) NOT NULL,
  `email` varchar(75) NOT NULL,
  `received` varchar(3) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=69 ;

--
-- Dumping data for table `campaignRecipts`
--

INSERT INTO `campaignRecipts` (`ID`, `campaignID`, `email`, `received`) VALUES

Edited by pritaeas: Removed emails. They seem real, posting them is not recommended.

0

I copied your data to try when I get home. If you want others to help, I suggest you post dummy data instead.

0

I couldn't run the query above, as you did not post the structure and (test) data for the maillist table. Be careful what you post this time though.

1

My apology! Here is the dummy data for table maillist...

-- --------------------------------------------------------

--
-- Table structure for table `maillist`
--

DROP TABLE IF EXISTS `maillist`;
CREATE TABLE IF NOT EXISTS `maillist` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `parentid` varchar(11) NOT NULL,
  `childrenCtn` int(3) NOT NULL,
  `fname` varchar(30) NOT NULL DEFAULT '',
  `email` varchar(55) NOT NULL DEFAULT '',
  `address` varchar(55) NOT NULL,
  `city` varchar(25) NOT NULL,
  `state` varchar(2) NOT NULL,
  `zip` int(5) NOT NULL,
  `referral` varchar(20) NOT NULL,
  `urgency` varchar(20) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `lname` varchar(45) NOT NULL,
  `cust_status` varchar(12) NOT NULL,
  `grpType` varchar(20) NOT NULL,
  `addToSumGrp` date NOT NULL,
  `joined_date` datetime NOT NULL,
  `toured_on` date NOT NULL,
  `notes` longtext NOT NULL,
  `webRequest` varchar(20) NOT NULL,
  `webRequest_filled` varchar(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `parentid` (`parentid`),
  KEY `name` (`fname`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

--
-- Dumping data for table `maillist`
--

INSERT INTO `maillist` (`id`, `parentid`, `childrenCtn`, `fname`, `email`, `address`, `city`, `state`, `zip`, `referral`, `urgency`, `phone`, `lname`, `cust_status`, `grpType`, `addToSumGrp`, `joined_date`, `toured_on`, `notes`, `webRequest`, `webRequest_filled`) VALUES
(2, 'MB8138', 0, 'Moses', 'clientcare0192@toolboxes.com', '', '', '', 0, '', '', '877-432-7525', 'Gordan', 'yes', '', '0000-00-00', '2015-03-24 09:11:06', '0000-00-00', '', '', ''),
(3, 'MS8394', 1, 'Maria', 'playtt@dssssc.net', '', '', '', 0, 'Sign', '1 Month', '202-343-3343', 'Smith', 'yes', '', '0000-00-00', '2015-03-24 16:24:42', '2015-03-24', '', '', ''),
(4, 'MA2976', 1, 'Megan', 'smartspin322tt@aol.com', '', '', '', 0, 'Sign', '1-2 Weeks', '202-322-3332', 'Awilson', 'yes', '', '0000-00-00', '2015-03-25 18:21:27', '2015-03-25', '', '', ''),
(18, 'JM9543', 1, 'Jason', 'admin@thfddaclc.com', '', '', '', 0, 'Mailer', '1 Month', '203-232-2323', 'Michael', 'no', '', '0000-00-00', '2015-03-31 08:07:41', '0000-00-00', '', 'tour', 'yes'),
(6, 'AW3447', 2, 'Alfred', 'support3@autotggpro.net', '', '', '', 0, 'Friend', '1 Month', '202-323-2324', 'Wilson', 'yes', '', '0000-00-00', '2015-03-25 20:37:36', '0000-00-00', '', 'tour', 'yes'),
(7, 'FM8479', 1, 'Frank', 'smartspin43@webmail.com', 'No Address Data', 'No Data', 'No', 0, 'Mailer', '1-2 Weeks', '202-323-2223', 'Mills', 'yes', '', '0000-00-00', '2015-03-26 09:21:47', '0000-00-00', '', 'tour', 'yes'),
(8, 'MD4881', 1, 'Monica', 'mdavisfddee@gmail.com', '', '', '', 0, 'Mailer', 'Future', '232-223-2322', 'Davis', 'no', '', '0000-00-00', '2015-03-26 09:52:36', '0000-00-00', '', 'tour', 'yes'),
(9, 'SG6318', 1, 'Susan', 'sgoldstedss@gmail.com', '', '', '', 0, 'Friend', '1 Month', '202-223-3222', 'Goldstein', 'no', '', '0000-00-00', '2015-03-26 10:03:25', '0000-00-00', '', 'tour', 'yes'),
(10, 'MW6968', 1, 'Michael', 'mwilson@gmail.com', '', '', '', 0, 'Friend', '1 Month', '823-232-3222', 'Wilson', '', '', '0000-00-00', '2015-03-26 10:06:33', '0000-00-00', '', 'tour', 'no'),
(12, 'OP7196', 1, 'Olivia', 'NOMAIL_oparks@yahoo.com', '', '', '', 0, 'Mailer', '1 Month', '203-232-3222', 'Parks', 'no', '', '0000-00-00', '2015-03-26 13:54:20', '0000-00-00', '', '', ''),
(13, 'PD7135', 1, 'Pauline', 'smartspintt33@yahds.com', '', '', '', 0, '0', '1 Month', '202-323-2322', 'Davis', 'no', '', '0000-00-00', '2015-03-28 06:43:01', '2015-03-30', '', '', '');

For proper data search from both tables here is the dummy data for table campaignRecipts -- with similar dummy data vals

--
-- Table structure for table `campaignRecipts`
--

DROP TABLE IF EXISTS `campaignRecipts`;
CREATE TABLE IF NOT EXISTS `campaignRecipts` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `campaignID` varchar(12) NOT NULL,
  `email` varchar(75) NOT NULL,
  `received` varchar(3) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

--
-- Dumping data for table `campaignRecipts`
--

INSERT INTO `campaignRecipts` (`ID`, `campaignID`, `email`, `received`) VALUES
(1, '26666414', 'playtt@dssssc.net', 'yes'),
(2, '673285', 'support3@autotggpro.net', 'yes'),
(3, '6198714', 'smartspin322tt@aol.com', 'yes'),
(4, '6609814', 'support3@autotggpro.net', 'yes'),
(5, '731260', 'smartspin43@webmail.com', ''),
(6, '591860', 'mdavisfddee@gmail.com', ''),
(7, '960412', 'sgoldstedss@gmail.com', ''),
(8, '9733417', 'clientcare0192@toolboxes.com', 'yes'),
(9, '9949107', 'clientcare0192@toolboxes.com', 'yes'),
(10, '193274', 'smartspintt33@yahds.com', 'yes'),
(11, '4062847', 'clientcare0192@toolboxes.com', 'yes'),
(12, '6941090', 'clientcare0192@toolboxes.com', 'yes'),
(13, '704831', 'admin@thfddaclc.com', ''),
(14, '456831', 'admin@thfddaclc.com', ''),
(15, '803417', 'admin@thfddaclc.com', ''),
(16, '8979581', 'clientcare0192@toolboxes.com', 'yes'),
(17, '1467868', 'clientcare0192@toolboxes.com', 'yes'),
(18, '1467868', 'playtt@dssssc.net', 'yes'),
(19, '1467868', 'smartspin43@webmail.com', ''),
(20, '9041261', 'clientcare0192@toolboxes.com', ''),
(21, '9041261', 'playtt@dssssc.net', ''),
(22, '9041261', 'support3@autotggpro.net', ''),
(23, '9041261', 'smartspin43@webmail.com', ''),
(24, '5838742', 'clientcare0192@toolboxes.com', ''),
(25, '5838742', 'playtt@dssssc.net', ''),
(26, '5838742', 'smartspin322tt@aol.com', ''),
(27, '5838742', 'support3@autotggpro.net', ''),
(28, '5838742', 'smartspin43@webmail.com', '');

thank you!

Edited by mbarandao

1

Hi mbarandao

I think you don't really needs the
GROUP BY ttlsent ASC
because you are missing some rows

I will give you an sql with all the rows

SELECT 
    email,
    fname,
    lname,
    ttlsent,
    ttlreceived,
    ROUND(ttlreceived / ttlsent * 100, 2) percentage
FROM
    (SELECT 
        cr.email,
            m.fname,
            m.lname,
            SUM(1) ttlsent,
            SUM(CASE
                WHEN cr.received = 'yes' THEN 1
                ELSE 0
            END) ttlreceived
    FROM
        campaignRecipts AS cr, maillist AS m
    WHERE
        cr.email = m.email
    GROUP BY cr.email , m.fname , m.lname) data
ORDER BY ttlsent ASC

1st you can change the
ROUND(ttlreceived / ttlsent * 100, 2) percentage
to
ROUND(ttlreceived / ttlsent * 100, 0) percentage
to strip out the decimals

2nd replace the last ORDER with GROUP to get the results as your query

George

0

George, thank you very much for your thoughts on this. I will give it a try and report back.

Thanks again!
Mossa

0

George, your suggested solution works great! Thanks again...

If I may ask another related question, I would like to add to the query a search between two dates using:

 STR_TO_DATE('2015-03-01', "%Y-%m-%d") <= `dateSent` AND STR_TO_DATE('2015-03-30', "%Y-%m-%d") >= `dateSent`

this date specficity is done on another table that contains dates and references of all email campaigns and their sent date.

I have modified the query to something like:

SELECT 
    email,
    fname,
    lname,
    cust_status,
dateSent,
    ttlsent,
    ttlreceived,
    ROUND(ttlreceived / ttlsent * 100, 2) percentage
FROM
    (SELECT 
            cr.email,
            m.fname,
            m.lname,
            m.cust_status,
         e.dateSent,
            SUM(1) ttlsent,
            SUM(CASE

                WHEN cr.received = 'yes' THEN 1
                ELSE 0
            END) ttlreceived 
    FROM
        campaignRecipts AS cr, maillist AS m, emailsSent AS e
    WHERE
        cr.email = m.email and m.cust_status='staff' and  STR_TO_DATE('2015-03-01', "%Y-%m-%d") <= `dateSent` AND STR_TO_DATE('2015-03-30', "%Y-%m-%d") >= `dateSent`
    GROUP BY cr.email , m.fname , m.lname) data
ORDER BY percentage desc

I need some help with structuring the modification properly!

I appreciate any thoughts on this!

Thanks,
Mossa

Edited by mbarandao

0

Hi Mossa

Nice to hear that it works for you!

You have to read a lite more about grouping and table joining

In your 2nd query, how is the table emailsSent joined with the other two tables?

and in the group by section you have to add all the columns that are not in an aggregate function

try GROUP BY cr.email , m.fname , m.lname, m.cust_status, e.dateSent) data

but without a proper join of emailsSent you will get wrong results

George

0

George,

I appreciate the response. Indeed, more reading is in order --I am on it!

To your question:

In your 2nd query, how is the table emailsSent joined with the other two tables?

I only have date sent, sentby, email subject, campaign id and total sent to. See image of table structure below.

Google_ChromeScreenSnapz019.jpg

Perhaps, my date range needs to come from a regular calendar dates and not dates inserted in the emailsSent table --since there isn't a specific joining field.

So essentially, I would want to run the existing query against specified date range. Does this mean that I would need to create separate table with dates?

Edited by mbarandao

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.