0

Here is a query i have and need to display results in column wise. But from this query i am getting all results in one column. 

SELECT 
     ( SELECT COUNT(DISTINCT asn) 
         FROM table_name  
        WHERE 1=1 
          AND status IN('Failure','saved','Successful') 
          AND DATE(created_on) BETWEEN '20161020' AND '20161130'
     ) status

But i need result some thing like 

Failure | saved | Successful
12           10         15

How can i acheive this ? Any help would be greatly appreciated.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
user3408779
  • 981
  • 2
  • 19
  • 43
  • SELECT SUM(CASE WHEN ... THEN 1 ELSE 0 END) failure, SUM(CASE...)... FROM... GROUP BY... – Strawberry Nov 11 '16 at 14:38
  • @Strawberry pls use your hammer, provided the duplicate topic link – Shadow Nov 11 '16 at 14:46
  • I have achieved this in the below way. SELECT (SELECT COUNT(DISTINCT asn) FROM table_name WHERE `status` = 'Failure' AND DATE(created_on) BETWEEN '20161020' AND '20161130') Failure, (SELECT COUNT(DISTINCT asn) FROM table_name WHERE `status` = 'saved' AND DATE(created_on) BETWEEN '20161020' AND '20161130') saved , (SELECT COUNT(DISTINCT asn) FROM table_name WHERE `status` = 'Successful' AND DATE(created_on) BETWEEN '20161020' AND '20161130') Successful Is there any other better way to query this? – user3408779 Nov 11 '16 at 14:50
  • @Shadow Am I the only one with a hammer? How can that be? – Strawberry Nov 11 '16 at 15:04
  • Pivots are by their nature known to be ugly looking queries because you have to specify your data in an unnatural way in the query (and modify accordingly as your data for types/statuses/whatever change) – Drew Nov 11 '16 at 15:06
  • on another note, mysql community, please see Shadow's answers and try to reward him a few points every week ... he works tirelessly to call out dupes to help people :p – Drew Nov 11 '16 at 15:07

0 Answers0