1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

What does this query mean? Am I correct?

Discussion in 'General' started by Bnat, Mar 18, 2014.

  1. Bnat

    Bnat Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    select AVG(cnt)
    from (
    select DriverID,cast(COUNT(9) as float) cnt
    from myTEST2 a
    where exists (select 1 from myTEST2
    where DriverID = a.DriverID
    and disposition='Guilty'
    )
    group by DriverID
    ) x

    I think its calculating the average count of people with the disposition of guilty.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    you sql-query :


    Code (SQL):



    WITH
    you_data AS
    (
    SELECT 1 DriverID , 'John Fname L', 'Doe' name , 19651025  DOB ,'Not Guilty' Disposition FROM dual UNION ALL    
    SELECT 2 ,'Frank' ,'Wright', 19770115 ,'Guilty' FROM dual UNION ALL
    SELECT 2 ,'Frank','Wright', 19770115 ,'Guilty'  FROM dual UNION ALL
    SELECT 3 ,'Ed', 'Jones', 19810604 ,'Guilty' FROM dual UNION  ALL
    SELECT 4, 'Mary', 'Jones', 19800730 , 'Not Guilty' FROM dual UNION ALL
    SELECT 4 ,'Mary','Jones', 19800730 ,'Guilty' FROM dual  UNION ALL
    SELECT 5 ,'Larry',' Able', 19771201,' Not Guilty' FROM dual
    )
    SELECT
      avg(cnt)
    FROM (
    SELECT driverid ,COUNT(*) cnt

    FROM you_data
    WHERE
        Disposition = 'Guilty'
    GROUP BY driverid    
    );



    SQL >

      AVG(CNT)
    ----------
    1.333333333

     
    Understand this example - here everything very simply.
    Obtain detail data, look and calculate AVG in manual and all of you will understand


    See also :
    SQL COUNT() Function
    SQL AVG() Function
     
  3. Bnat

    Bnat Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Here is the table being used:

    DriverID Fname Lname DOB Disposition
    1 John Doe 19651025 Not Guilty
    2 Frank Wright 19770115 Guilty
    2 Frank Wright 19770115 Guilty
    3 Ed Jones 19810604 Guilty
    4 Mary Jones 19800730 Not Guilty
    4 Mary Jones 19800730 Guilty
    5 Larry Able 19771201 Not Guilty
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    you sql-query :


    Code (SQL):



    WITH
    you_data AS
    (
    SELECT 1 DriverID , 'John Fname L', 'Doe' name , 19651025  DOB ,'Not Guilty' Disposition FROM dual UNION ALL    
    SELECT 2 ,'Frank' ,'Wright', 19770115 ,'Guilty' FROM dual UNION ALL
    SELECT 2 ,'Frank','Wright', 19770115 ,'Guilty'  FROM dual UNION ALL
    SELECT 3 ,'Ed', 'Jones', 19810604 ,'Guilty' FROM dual UNION  ALL
    SELECT 4, 'Mary', 'Jones', 19800730 , 'Not Guilty' FROM dual UNION ALL
    SELECT 4 ,'Mary','Jones', 19800730 ,'Guilty' FROM dual  UNION ALL
    SELECT 5 ,'Larry',' Able', 19771201,' Not Guilty' FROM dual
    )
    SELECT
      avg(cnt)
    FROM (
    SELECT driverid ,COUNT(*) cnt

    FROM you_data
    WHERE
        Disposition = 'Guilty'
    GROUP BY driverid    
    );


     


    See also :
    SQL COUNT() Function
    SQL AVG() Function
     
    Bnat likes this.
  5. Bnat

    Bnat Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thank you Sergey! this makes it easier to understand.