Help with this mysql query

[mbarandao]

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,

[pritaeas]

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.

[mbarandao]

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

[pritaeas]

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

[mbarandao]

I appreciate that

[pritaeas]

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.

[mbarandao]

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', '[e-mail]', '', '', '', 0, '', '', '877-432-7525', 'Gordan', 'yes', '', '0000-00-00', '2015-03-24 09:11:06', '0000-00-00', '', '', ''),
(3, 'MS8394', 1, 'Maria', '[e-mail]', '', '', '', 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', '[e-mail]', '', '', '', 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', '[e-mail]', '', '', '', 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', '[e-mail]', '', '', '', 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', '[e-mail]', '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', '[e-mail]', '', '', '', 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', '[e-mail]', '', '', '', 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', '[e-mail]', '', '', '', 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', '[e-mail]', '', '', '', 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', '[e-mail]', '', '', '', 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', '[e-mail]', 'yes'),
(2, '673285', '[e-mail]', 'yes'),
(3, '6198714', '[e-mail]', 'yes'),
(4, '6609814', '[e-mail]', 'yes'),
(5, '731260', '[e-mail]', ''),
(6, '591860', '[e-mail]', ''),
(7, '960412', '[e-mail]', ''),
(8, '9733417', '[e-mail]', 'yes'),
(9, '9949107', '[e-mail]', 'yes'),
(10, '193274', '[e-mail]', 'yes'),
(11, '4062847', '[e-mail]', 'yes'),
(12, '6941090', '[e-mail]', 'yes'),
(13, '704831', '[e-mail]', ''),
(14, '456831', '[e-mail]', ''),
(15, '803417', '[e-mail]', ''),
(16, '8979581', '[e-mail]', 'yes'),
(17, '1467868', '[e-mail]', 'yes'),
(18, '1467868', '[e-mail]', 'yes'),
(19, '1467868', '[e-mail]', ''),
(20, '9041261', '[e-mail]', ''),
(21, '9041261', '[e-mail]', ''),
(22, '9041261', '[e-mail]', ''),
(23, '9041261', '[e-mail]', ''),
(24, '5838742', '[e-mail]', ''),
(25, '5838742', '[e-mail]', ''),
(26, '5838742', '[e-mail]', ''),
(27, '5838742', '[e-mail]', ''),
(28, '5838742', '[e-mail]', '');

thank you!

[kalxas]

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

[mbarandao]

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

Thanks again!
Mossa

[mbarandao]

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

[kalxas]

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

[mbarandao]

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.

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?