Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Getting Rid of Zero Counts

    I have this Query which will display Results though I only want a single line for each item (if number greater than 0 show it else show zero) but the query will show two rows for each country if it has a count, I want to use the number as a marker to highlight the row, without making requests when rendering my webform to determine the count?

    PHP Code:
    SELECT DISTINCT
                tCtry
    .CountryName,
                
    tCtry.CountryCode,
                
    ISNULL(cust.Number,0)    
            
    FROM 
                tblCustomers tC
            LEFT JOIN 
    (
                
    SELECT DISTINCT 
                        COUNT
    (tV.VehicleID) AS number,
                        
    tVTP.TypeID,
                        
    tV.VehicleID,
                        
    tCM.CustomerID 
                    FROM tblVehicles tV
                    INNER JOIN tblVehicleType_Policy tVTP
                    ON    tVTP
    .VehicleMaster tV.VehicleMaster
                        
    AND tV.VehicleModel tVTP.Model
                        
    AND tV.VehicleModelNo tVTP.ModelNo
                        
    AND tV.VehicleMake tVTP.Make
                        
    AND tV.VehicleChassisNo tVTP.Chassis
                        
    AND tVTP.TypeID = @PolicyID
                        
    AND (tVTP.Confirmed = @Confirmed OR @Confirmed=-1)
                    
    INNER JOIN tblCustomerMasters tCM
                    ON tCM
    .VehicleMaster tV.VehicleMaster            
                    GROUP BY
                        tVTP
    .TypeID,tV.VehicleID,tCM.CustomerID                
            
    cust ON cust.CustomerID tC.CustomerID AND number 0

                INNER JOIN tblCountries tCtry ON tCtry
    .CountryCode tC.CustomerCountry

            WHERE 
                tC
    .OfferPolicies 1
        
            ORDER BY     
                tCtry
    .CountryName 

    Shows
    PHP Code:
    CountryName                                        CountryCode Number
    -------------------------------------------------- ----------- -----------
    [
    COLOR="Red"]Australia                                          AUS         0
    Australia                                          AUS         3
    [/COLOR]
    Botswana                                           BWA         0
    France                                             FRA         0
    Haiti                                              HTI         0
    Japan                                              JPN         0
    Japan                                              JPN         1
    Morocco                                            MAR         0
    New Zealand                                        NZL         0
    Nigeria                                            NGA         0
    Trinidad 
    and Tobago                                TTO         0
    United States of America                           USA         0
    Yemen                                              YEM         0 

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    GROUP BY tCtry.CountryName

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    ?

    Make the left join an inner.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    No an Inner join Returns the Ones with only Numbers Greater than Zero, if a number exists there should be no number for it but if there isn't it should be zero, where distinct and group by don't do anything because the 0 and say 3 make them different when they shouldn't be?

    This does what I want but its real messy and I doubt its the best way to do it,

    PHP Code:
    CREATE TABLE #temp(
            
    CountryName nvarchar(50),
            
    CountryCode nvarchar(3),
            
    Number INT
            
    )

        
    CREATE TABLE #tmp(
            
    CountryName nvarchar(50),
            
    CountryCode nvarchar(3),
            
    Number INT
            
    )

    -- Do 
    some stuff with the table



        INSERT INTO 
    #temp 
        
    SELECT DISTINCT
                tCtry
    .CountryName,
                
    tCtry.CountryCode,
                
    ISNULL(cust.Number,0) AS NUMBER    
            FROM 
                tblCustomers tC
            LEFT JOIN 
    (
                
    SELECT DISTINCT 
                        COUNT
    (tV.VehicleID) AS number,
                        
    tVTP.TypeID,
                        
    tV.VehicleID,
                        
    tCM.CustomerID 
                    FROM tblVehicles tV
                    INNER JOIN tblVehicleType_Policy tVTP
                    ON    tVTP
    .VehicleMaster tV.VehicleMaster
                        
    AND tV.VehicleModel tVTP.Model
                        
    AND tV.VehicleModelNo tVTP.ModelNo
                        
    AND tV.VehicleMake tVTP.Make
                        
    AND tV.VehicleChassisNo tVTP.Chassis
                        
    AND tVTP.TypeID = @PolicyID
                        
    AND (tVTP.Confirmed = @Confirmed OR @Confirmed=-1)
                    
    INNER JOIN tblCustomerMasters tCM
                    ON tCM
    .VehicleMaster tV.VehicleMaster            
                    GROUP BY
                        tVTP
    .TypeID,tV.VehicleID,tCM.CustomerID                
            
    cust ON cust.CustomerID tC.CustomerID 

                INNER JOIN tblCountries tCtry ON tCtry
    .CountryCode tC.CustomerCountry

            WHERE 
                tC
    .OfferPolicies 1
        
            ORDER BY     
                tCtry
    .CountryName

            INSERT INTO 
    #tmp (CountryCode,CountryName) 
            
    SELECT CountryCode,CountryName FROM #temp
            
    WHERE Number<>0

        
            UPDATE  
    #tmp      
            
    SET Number =     ( SELECT MAX(#Temp.Number) FROM #Temp
            
    WHERE #Temp.CountryCode=#tmp.CountryCode )
            
    WHERE EXISTS
            
    (SELECT MAX(#Temp.Number) FROM #Temp
            
    WHERE #Temp.CountryCode=#tmp.CountryCode);

            
    INSERT INTO #tmp (CountryCode,CountryName,Number)
                
    SELECT CountryCode,CountryName,0 FROM #temp
                
    WHERE CountryCode NOT IN (SELECT CountryCode FROM #tmp)

            
    SELECT DISTINCT FROM #tmp 
            
    DROP TABLE #tmp 
            
    DROP TABLE #temp 
    results
    PHP Code:
    CountryName                                        CountryCode Number
    -------------------------------------------------- ----------- -----------
    Australia                                          AUS         13
    Botswana                                           BWA         0
    France                                             FRA         13
    Haiti                                              HTI         0
    Japan                                              JPN         15
    Morocco                                            MAR         0
    New Zealand                                        NZL         18
    Nigeria                                            NGA         0
    Trinidad 
    and Tobago                                TTO         0
    United States of America                           USA         0
    Yemen                                              YEM         0 

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    start by figuring out why you have to have all those horrible DISTINCTs in there

    that should get you half way home...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this not simply a case of using MAX?

    ....getting rid of the distincts is nice and tidy too.

  7. #7
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I am pretty sure that
    SELECT
    tCtry.CountryName,
    tCtry.CountryCode,
    SUM(ISNULL(cust.Number,0))
    ...
    GROUP BY tCtry.CountryName

    is the solution you need.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aflorin27
    I am pretty sure that <snip> is the solution you need.
    nope, you have a syntax error, your SELECT clause doesn't agree with teh GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Hmm, haven't seen the syntax error Will have another look tomorrow, usually it won't run at all with a syntax error, unless theres another sort? Could you be more specific? Also how wasteful are temp tables?

  10. #10
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Is this not simply a case of using MAX?

    ....getting rid of the distincts is nice and tidy too.

    It could be just a case of max Ill have another Look may have made it more complex than what it needs to be.

  11. #11
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by r937
    nope, you have a syntax error, your SELECT clause doesn't agree with teh GROUP BY clause
    Oh yes, I forgot:
    GROUP BY tCtry.CountryName, tCtry.CountryCode

Posting Permissions

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