Search the Community

I would like to simplify my request... here is my query SELECT emp_id, ROUND(SUM((Invoice.invoiceTotal-NewQuote.total)*.30),2) AS thirtypercent, ROUND(SUM(NewQuote.total*.20),2) AS twentypercent FROM `NewQuote` INNER JOIN Invoice ON Invoice.quoteID=NewQuote.quoteID INNER JOIN schedules ON schedules.quoteId=NewQuote.quoteID WHERE (rem=1 OR rem=2) AND (schedules.redo IS NULL) AND invoiceTotal!=0 AND (DATE_FORMAT(date_start, '%m/%d/%Y') >= '07/01/2016') AND ((DATE_FORMAT(date_start, '%m/%d/%Y')<='07/31/2016')) GROUP BY emp_id What im aiming here is this line "AND invoiceTotal!=0" should only be used in getting the thirtypercent and should not be applied in getting the twentypercent. Can you help me on how i can do this? Thank you

I have this query that fully works the way it is, but I am wondering if I am overdoing some things in it. I know this may be hard to tell without knowing the full relationship between all the tables in the query. I'm basically running 3 subqueries mostly based on a timestamp value and other specific clauses per subquery. Where the main query is only gathering based on the timestamp. Like I said it works perfect from what I can verify by cross referencing the tables manually and checking the results the query returned. I just want to know if there is a better way to do all this. SELECT `products`.`id` AS `pid`, `products`.`prod_name`, (SELECT COUNT(*) FROM `products` INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id` INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id` WHERE `quote_responses`.`purchased` = 0 AND `quote_deposits`.`dep_date` >= $committed_start ) AS `committed`, (SELECT COUNT(`products`.`id`) FROM `products` INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id` INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id` WHERE `quote_responses`.`purchased` = 1 AND `products`.`id` = `pid` AND `quote_deposits`.`dep_date` >= $committed_start ) AS `total_per_item`, (SELECT COUNT(`products`.`id`) FROM `products` INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id` INNER JOIN `quote_responses` ON `quote_responses`.`id` = `quote_deposits`.`q_id` INNER JOIN `schedule` ON `schedule`.`deposit_id` = `quote_deposits`.`id` WHERE `quote_responses`.`purchased` = 0 AND `schedule`.`cancelled` != '' AND `products`.`id` = `pid` AND `quote_deposits`.`dep_date` >= $committed_start ) AS `total_cancelled` FROM `products` INNER JOIN `quote_deposits` ON `quote_deposits`.`product_id` = `products`.`id` WHERE `quote_deposits`.`dep_date` >= $committed_start GROUP BY `products`.`prod_name` ORDER BY `products`.`prod_name` ASC

I am having trouble writing exactly 4 queries in mysql. Here is the view I am using for queries: http://pastie.org/8403299 Here is the Raw Data you can check http://goo.gl/z7Vv9X which you can check. What is needed: 1) Gained students are those who exist in the selected / filtered month and don't exist in the previous month. 2) Lost students are those who exist in the previous month of the selected / filtered month and don't exist in the selected / filtered month. 3) Get a list of all lost students since the start of the center till now(). 4) Get a list of all gained students since the start of center till now. I am currently stuck with query 1) (Gained students are those who exist in the selected / filtered month and don't exist in the previous month.) I have written it like that (see just below), but I get the same number of rows back if I haven't filtered anything... uff. SELECT * FROM lessons_master_001 my1 WHERE NOT EXISTS ( SELECT 0 FROM lessons_master_001 my2 WHERE my1.student_id = my2.`student_id` AND my1.teacher_id = my2.teacher_id AND my1.`student_payment_id`= my2.`student_payment_id`AND CONCAT(CAST(MONTH(DATE_ADD(my1.Lesson_Booking_Date, INTERVAL -1 MONTH))AS CHAR(20)),CAST(YEAR(DATE_ADD(my1.Lesson_Booking_Date, INTERVAL -1 MONTH))AS CHAR(20))) = CONCAT(CAST(MONTH(my2.`Lesson_Booking_Date`)AS CHAR(20)),CAST(YEAR(my2.`Lesson_Booking_Date`)AS CHAR(20))) ) My logic is like this so I take everything from the view lessons_master_001 and give alias my1 and in the NOT EXISTS clause I am giving to the same dataset alias my2, then join those ID's that are also used in view lessons_master_001 and then try to connect previous month from data set my1 with current month from data set my2. I am using DATE_ADD function to subtract month from current date and practically get date - 1 month. I would really appreciate any help you will provide, cause I lost 2 days already on it with no progress in front. Thank you in advance.

Hi, I have two MySQL queries - each pulls the exact information I need when loaded on its own via CLI. However, I need to join the two - insert one as a subquery - so I can then use the 'COUNT(cl.reqid)' results for each grouped set of data results parsed from that query into the later in order to complete the 'epc' calculation. Below are the two quereis - I know the "GROUP BY" clauses creating the multiple rows but only way I could segment the data (per date and a.affid and s.subid) to show individual sets of data to get correct total count of cl.reqid. I need help combining the two queries so I can then calculate the epc (that formula will be: (c.payout * COUNT(l.reqid) / COUNT(cl.reqid) as epc) - which will be added to the combined query. BELOW is the main query - it runs without issues (as is) SELECT DATE(l.time1) as date,a.aname,s.subid, COUNT(l.reqid),c.camp_name,c.campid FROM leads l LEFT JOIN affiliates AS a ON l.affid = a.affid LEFT JOIN subaffiliates AS s ON l.subid = s.subaffid LEFT JOIN campaign AS c ON l.campid = c.campid WHERE (a.affid = l.affid) AND (l.subid = s.subaffid) AND (l.campid = c.campid) GROUP BY DATE(l.time1),a.aname,s.subid ORDER BY a.aname,DATE(l.time1) ASC; Next (below) is the 2nd query to which I need to include into the above query - I assume as a subquery and would prefer to have the 'COUNT(cl.reqid)' value display right after ,'s.subid'. Also, I need to use both 'COUNT(l.reqid)' and 'COUNT(cl.reqid)' to perform the 'epc' calculation. SELECT a.aname,s.subid,DATE(cl.cdate),COUNT(cl.reqid),c.camp_name,c.campid from clicks AS cl LEFT JOIN affiliates as a ON a.affid = cl.affid_sid LEFT JOIN subaffiliates as s ON s.subaffid = cl.subid LEFT JOIN campaign as c ON c.campid = cl.camp_sid WHERE (a.affid = cl.affid_sid) AND (s.subid != '') AND (s.subaffid = cl.subid) AND (c.campid = cl.camp_sid) GROUP BY DATE(cdate),a.aname,s.subid ORDER BY a.aname,DATE(cdate); I tried this (see below) just to see what I would get - the 'multiple rows' error due to repeating date from DATE(cl.date) I know but now sure how to handle that... BTW: that date (cl.cdate) does not match (l.time1) - two separate entried so can't use those to pair records. Anyway, this is what I came up with thus far to which i need help working into correct structure: SELECT DATE(l.time1) as date,a.aname,s.subid, (SELECT a.aname,s.subid,DATE(cl.cdate),COUNT(cl.reqid),c.camp_name,c.campid,c.payout from clicks AS cl LEFT JOIN affiliates as a ON a.affid = cl.affid_sid LEFT JOIN subaffiliates as s ON s.subaffid = cl.subid LEFT JOIN campaign as c ON c.campid = cl.camp_sid WHERE (a.affid = cl.affid_sid) AND (s.subid != '') AND (s.subaffid = cl.subid) AND (c.campid = cl.camp_sid) GROUP BY DATE(cdate),a.aname,s.subid ORDER BY a.aname,DATE(cdate)), COUNT(l.reqid),c.camp_name,c.campid (c.payout * COUNT(l.reqid) / COUNT(cl.reqid) as epc) FROM leads l LEFT JOIN affiliates AS a ON l.affid = a.affid LEFT JOIN subaffiliates AS s ON l.subid = s.subaffid LEFT JOIN campaign AS c ON l.campid = c.campid WHERE (a.affid = l.affid) AND (l.subid = s.subaffid) AND (l.campid = c.campid) GROUP BY DATE(l.time1),a.aname,s.subid ORDER BY a.aname,DATE(l.time1) ASC; Really appreciate if someone can explain how I can combine the 1st two to form the correct subquery you see in last post (I know that one is wrong due to "GROUP BY" clause - just not sure how to address. Thanks for help!

I am a beginner with MySQL and was wondering what method advanced users would recommend to perform the simple task of looking through two tables to find one row. I don't need code from you, just a general idea of what type of query is best for this situation. If you can offer an explanation of why you like the method, that's even better. Thanks!