#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    60
    Rep Power
    10

    Exclamation MYSQL Query Help


    app

    Code:
    AppNumber	|	idClient	|	idGroup	|	idDealer	|	AppDate		| 	AppStatus	|	CallStatus
    	1			3				15			1				2017-01-01		ACTIVE			CONCLUDED
    	2			3				15			1				2017-01-01		INACTIVE		NTU
    	3			3				15			1				2017-01-01		ACTIVE			ASSIGNED
    	4			3				15			1				2017-01-01		ACTIVE			CONCLUDED
    	5			3				18			2				2017-01-01		ACTIVE			CONCLUDED
    	6			3				18			2				2017-01-01		ACTIVE			CONCLUDED
    	7			3				18			2				2017-01-01		ACTIVE			ASSIGNED
    	8			3				18			2				2017-01-01		ACTIVE			CONCLUDED
    	9			3				18			2				2017-01-01		ACTIVE			CONCLUDED
    
    
    
    dealer
    
    idDealer	|	DealerCode	|	DealerName	|	Status	|	Target	| idClient	|	idGroup	|
    	1			ABC123			BATMAN CC		ACTIVE		100			3			15
    	2			ABC125			ROBIN CC		ACTIVE		100			3			18
    	3			ABC126			SUPERMAN CC		ACTIVE		100			3			18	
    	
    SELECT DISTINCT COUNT(app.AppNumber)AS Total, app.idClient, app.idGroup, app.idDealer,
    			       dealer.Target, dealer.DealerCode FROM app, dealer WHERE app.idClient='3' AND app.idGroup='15' AND
    			       AppDate BETWEEN '2017-01-01' AND '2017-01-02' AND AppStatus='ACTIVE' AND
    			       app.idDealer=dealer.idDealer GROUP BY idDealer ORDER BY Total DESC
    This gives me a result with the Dealer and the amount of Applications that are done and the amount of concluded apps for Dealers that have made applications

    I however need to display as below where dealers that have not made any applications display 0

    Code:
    Dealer		|	DealerCode	|	AppTotal	|	Concluded	| 	Target
    BATMAN CC		ABC123 				3				2   		100
    ROBIN CC		ABC125				5				4			100
    SUPERMAN CC		ABC126				0				0			100
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,656
    Rep Power
    4288
    Code:
    SELECT dealer.idDealer
         , dealer.Target
         , dealer.DealerCode 
         , COUNT(app.AppNumber) AS Total
         , MIN(app.idClient) AS idClient
         , MAX(app.idGroup)  AS idGroup 
      FROM dealer
    LEFT OUTER
      JOIN app
        ON app.idDealer = dealer.idDealer  
       AND app.idClient = 3 
       AND app.idGroup = 15 
       AND app.AppDate BETWEEN '2017-01-01' AND '2017-01-02' 
       AND app.AppStatus = 'ACTIVE' 
    GROUP 
        BY idDealer 
    ORDER 
        BY Total DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    60
    Rep Power
    10
    Hi

    Many thanks for the assistance it is much appreciated.

    When I run that query on my actual data I get results that are from other groups as well, it should only show me results from idClient 3 and idGroup 15

    idGroup 15 only has 79 dealers and the result gives me 421 rows, so it looks like it is showing me all results from all apps table

    could we not run the query from the dealer table that shows only dealers that belong to group 15 then join the amount of apps to that?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,656
    Rep Power
    4288
    are you implying that idClient and idGroup are columns of the dealer table, not of the app table?

    if they are not columns of the app table, my query should not have run at all

    if they are columns of both tables, then WTF, man?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,616
    Rep Power
    1766
    Yes, they look to be columns in both tables, so very much a WTF?!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    60
    Rep Power
    10
    Originally Posted by SimonJM
    Yes, they look to be columns in both tables, so very much a WTF?!
    Hi

    Yes they are, why is that a problem?

    Also I am unsure why you are using MIN and MAX
    Last edited by fabzster; January 23rd, 2017 at 01:09 AM.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,656
    Rep Power
    4288
    Originally Posted by fabzster
    Hi
    Yes they are, why is that a problem?
    because of the redundancy, and possibility for mismatch

    Also I am unsure why you are using MIN and MAX
    because otherwise you'll get random values from the entire group, which is meaningless

    Code:
    SELECT dealer.idDealer
         , dealer.Target
         , dealer.DealerCode 
         , COUNT(app.AppNumber) AS Total
         , MIN(app.idClient) AS idClient
         , MAX(app.idGroup)  AS idGroup 
      FROM dealer
    LEFT OUTER
      JOIN app
        ON app.idDealer = dealer.idDealer  
       AND app.AppDate BETWEEN '2017-01-01' AND '2017-01-02' 
       AND app.AppStatus = 'ACTIVE' 
     WHERE dealer.idClient = 3 
       AND dealer.idGroup = 15 
    GROUP 
        BY idDealer 
    ORDER 
        BY Total DESC

    Comments on this post

    • fabzster agrees : Many thanks this is exactly what I was looking for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    60
    Rep Power
    10
    Originally Posted by r937
    because of the redundancy, and possibility for mismatch



    because otherwise you'll get random values from the entire group, which is meaningless

    Code:
    SELECT dealer.idDealer
         , dealer.Target
         , dealer.DealerCode 
         , COUNT(app.AppNumber) AS Total
         , MIN(app.idClient) AS idClient
         , MAX(app.idGroup)  AS idGroup 
      FROM dealer
    LEFT OUTER
      JOIN app
        ON app.idDealer = dealer.idDealer  
       AND app.AppDate BETWEEN '2017-01-01' AND '2017-01-02' 
       AND app.AppStatus = 'ACTIVE' 
     WHERE dealer.idClient = 3 
       AND dealer.idGroup = 15 
    GROUP 
        BY idDealer 
    ORDER 
        BY Total DESC

    Thank you soooo much it looks like I am getting the correct results now

IMN logo majestic logo threadwatch logo seochat tools logo