-1

I have a query given below 

SELECT A.order_no, A.order_date,
COUNT(B.reaction_no) as tot_reaction_no, 
SUM(CASE 
WHEN (B.purification != '') THEN 1 
ELSE 0  
END) as tot_purification 
FROM order_header A
LEFT JOIN order_reactions B ON A.order_no = B.order_no
WHERE A.order_date BETWEEN '2015-10-01 00:00:00' AND '2016-09-01 00:00:00' 
AND A.order_no = '23746'
GROUP BY  A.order_no

this will results as shown in the picture. But the result is wrong because some of the entries are duplicates. So I have to remove the duplicate and print the count. Count required is the count of "column" from the table 1.

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
V A S
  • 3,338
  • 4
  • 33
  • 39
  • Maybe you can use the distinct keyword in your query – DBX12 Nov 23 '16 at 07:11
  • 2
    Possible duplicate of [MySQL: Select only unique values from a column](http://stackoverflow.com/questions/8571902/mysql-select-only-unique-values-from-a-column) – Jacob Larson Nov 23 '16 at 07:11
  • total reaction count will change when i use DISTINCT – V A S Nov 23 '16 at 07:14
  • Also if I dare to criticize your design: Why exactly are you holding duplicates in your database? Your question seems to try to "cheat" around bad design instead of fixing said bad design. – DBX12 Nov 23 '16 at 07:18

3 Answers3

1

I think you need to leave out the A.order_date in your select or you should add it to the group by clause. That gives you a different result though.

You may also use a subquery in your select clause:

SELECT A.order_no, A.order_date, COUNT(B.reaction_no) as tot_reaction_no, (SELECT count(*) FROM order_reactions as or WHERE or.order_no=A.order_no AND purification!='') as tot_purification, (SELECT count(*) FROM order_reactions as or2 WHERE or.order_no=A.order_no) as tot_reaction_no FROM order_header A WHERE A.order_date BETWEEN '2015-10-01 00:00:00' AND '2016-09-01 00:00:00' AND A.order_no = '23746'

This is just from the top of my head, since your screenshots are not showing the full tables I'm not sure this is 100% right, but it might point you in the right direction.

Frank
  • 530
  • 5
  • 15
  • Seems like a good solution, there sure would be space for optimization, but Frank is right. Without knowing the complete table structure, it would be impossible to build a optimal query "right out of the head". – DBX12 Nov 23 '16 at 07:23
0

I would propose the query 

 SELECT COUNT(DISTINCT clone_name) AS tot_purification, COUNT(*) AS tot_reaction_no FROM Table2 WHERE `purification`='Column' AND `order_no`=23746;

Please excuse errors in quotation, MySQL is very confusing when it comes to quotation imo.

EDIT:

Added AS tot_purification

I'm not sure what you are expecting as tot_reaction_no, so I just counted all rows where Order and purification match as described in my WHERE clause.

DBX12
  • 2,041
  • 1
  • 15
  • 25
0

Its because you are grouping only on A.order_no, make following changes in the query and try again:

Replace the line:

GROUP BY A.order_no;

to 

GROUP BY A.order_no, A.clone_name;
Mayank Pandeyz
  • 25,704
  • 4
  • 40
  • 59