Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: MySQL query throwing unwanted results instead of zeros

    Please consider the following code for testing purpose:


    Code:
    CREATE TABLE Mydatabase(
      TableID_bi int,
      EVENTS VARCHAR(200),
      email VARCHAR(200),
      Timedetail DATETIME
    );
    
    INSERT INTO `Mydatabase` (`TableID_bi`,`EVENTS`, `email`, `Timedetail`) VALUES
      (1, 'first','email@gmail.com','2013-06-15' ),
    (2, 'first','email2@gmail.com', '2013-06-15'),
    (3, 'second','email3@gmail.com', '2013-06-15'),
    (4, 'second','email4@gmail.com', '2013-06-15'),
    (5, 'third','email5@gmail.com', '2013-06-15');

    A Brief Description of what I've done with the code:

    I have created a view for each of the connections, namely, first,second, third etc which is as
    followsThe reason I haven't chosen UNION to club all the queries is because I want the results to be displayed column wise rather than row.)

    Code:
    create view firstview as 
    SELECT  DATE(Timedetail) as Datefield1 ,
           count(Timedetail) as firstoccurances,
    		 Events  
    FROM Mydatabase  
    WHERE Events = "first" GROUP BY Datefield1 ;
    Similarly for second,third and all other connections.

    Here is how I'm retrieving the results from the view:

    Code:
    SELECT a.Datefield1
           a.firstoccurances,
           b.secondoccurances,
    	   c.thirdoccurances
    	   
    FROM   firstview a,
    	   secondview b,
    	   thirdview c
    	   
    WHERE a.Datefield1 = b.Datefield2
    AND   a.Datefield1 = c.Datefield3
    Brief Description about what's happening in the Database:

    I'm working on a large database of same kind as mentioned above. For example, I have a selected the folowing Date Range from the database: 2013-07-01 to
    2013-08-01

    Now, as far as first and second events are concerned I have "first" and "second" values listed for each and every date under the EVENTS column. To be
    more precise, if I test it using COUNT() function, I can see the results like following:

    For first connection:

    Code:
    TimeDetail  |  Number of first events
    2013-07-01              4
    2013-07-02              2
    2013-07-03              6
     and so on
     
    2013-08-01              5
    Same is the case with second event. However, with the "third" event, it's not the same.I have "third" event listed under Events column for only couple of
    dates, say for example 2013-07-02 and 2013-07-03 and I should see the following output:


    Code:
    TimeDetail  |  Number of third events
    2013-07-01              0
    2013-07-02              1
    2013-07-03              2
     and so on
     
    2013-08-01              0
    But I'm not getting the above output. Instead, all other date values except 2013-07-02 and 2013-07-03 are filled with value 1 under the "number of third" events column. May I know why?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT timedetail As datefield1
         , Count(CASE WHEN events = 'first' THEN timedetail END) As first_occurance
         , Count(CASE WHEN events = 'second' THEN timedetail END) As second_occurance
         , Count(CASE WHEN events = 'third' THEN timedetail END) As third_occurance
    FROM   mydatabase
    GROUP
        BY timedetail
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •