Results 1 to 17 of 17

Thread: [RESOLVED] SQL - Query to return sum and 0 if no match is found

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Resolved [RESOLVED] SQL - Query to return sum and 0 if no match is found

    Hi
    I have a table (HOC) containing the list of some items. I need to check another table (DA ) against HOC and sum up the occurrences of items and if no item is found then return 0 for sum.

    My query:
    Code:
    Select HOC.ListItemName,
    Sum(Case When DA.Hierarchy IS not null  then 1 else 0 End) As Total 
    
    --COALESCE(Sum(Case When DA.Hierarchy IS not null  then 1 else 0 End) , 0) As Total 
    
    From DV_ListItems as HOC 
    Left join DV_CaseOverview AS DA 
    On HOC.ListItemName = Cast(DA.HierarchyOrder as varchar(2)) + ': ' + DA.Hierarchy
    Join [DV_Data_NEW] As NG   
    ON NG.DEVIATION_NUMBER = DA.CaseID 
    Where
    HOC.ListItemType = 'DV Hierarchy' 
    And ( Format(NG.[Completed_DATE],'yyyy-MM-dd')  >= '2020-06-25' And Format(NG.[Completed_DATE],'yyyy-MM-dd')  <= '2020-09-25')  
    Group by HOC.ListItemName
    This query does not show all the relevant items that exist in HOC; and give me the sum of only occurrences:

    Name:  nullstuff.jpg
Views: 119
Size:  59.7 KB

    How can I fix this? is this because of how I join them? Is it because of
    Code:
    On HOC.ListItemName = Cast(DA.HierarchyOrder as varchar(2)) + ': ' + DA.Hierarchy
    and so if not both table contain the same item, then it won't show up in the result? but left join should be the correct method right?

    Thanks in advance for your help.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: SQL - Query to return sum and 0 if no match is found

    IT would have been... but then you inner joined here:
    Code:
    Join [DV_Data_NEW] As NG   
    ON NG.DEVIATION_NUMBER = DA.CaseID
    If there is no row in DA, then joining to NG negates the left join... your join to NG also needs to be left join
    AND
    you'll need to account for that here:
    Code:
    And ( Format(NG.[Completed_DATE],'yyyy-MM-dd')  >= '2020-06-25' And Format(NG.[Completed_DATE],'yyyy-MM-dd')  <= '2020-09-25')
    Since those date will not exist...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Re: SQL - Query to return sum and 0 if no match is found

    Thank you.
    How should I account for those dates; as soon as I use them then the result is messed up.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: SQL - Query to return sum and 0 if no match is found

    Well for starters, stop formatting them... formatting implies you care about what they look, which you shouldn't... they're dates... as you have it now, you're treating them like strings. Secondly if they don't exist, they would be null, so check for their null-ness.



    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Re: SQL - Query to return sum and 0 if no match is found

    I got you about the format.
    But, then those dates jsut set the period for the data range I am interested in. It is all good for the rest of the items. Waht I want to see is that there is no "lagging Control" or rather a row containing: "1: Lagging Control = 0" and the rrest of the stuff. I am sure that there is no cases inthat period that is marked with "lagging Control" and so I expected to see a row with zero as sum.
    Last edited by Grand; Sep 25th, 2020 at 03:21 PM.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: SQL - Query to return sum and 0 if no match is found

    Like I said... you need to test for the null-ness of the dates:
    Code:
    Select HOC.ListItemName,
    Sum(Case When DA.Hierarchy IS not null  then 1 else 0 End) As Total 
    From DV_ListItems as HOC 
    Left join DV_CaseOverview AS DA 
      On HOC.ListItemName = Cast(DA.HierarchyOrder as varchar(2)) + ': ' + DA.Hierarchy
    left Join [DV_Data_NEW] As NG   
      ON NG.DEVIATION_NUMBER = DA.CaseID 
    Where
      HOC.ListItemType = 'DV Hierarchy' 
      And (
        (NG.[Completed_DATE]  between '2020-06-25' And '2020-09-25')
       or
        (NG.[Completed_DATE] is null)
      )
    Group by HOC.ListItemName
    I'm assuming you're using SQL Server...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Re: SQL - Query to return sum and 0 if no match is found

    Thank you so much for your tireless effort here.

    I am however getting the same result
    just a side question, why those dates are important here adn more specifically why they should be null? isn't the HOC.ListItemName I should focus on?

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: SQL - Query to return sum and 0 if no match is found

    Follow:
    You have the following:
    Table A
    1 -- Item 1
    2 -- Item 2
    3 -- Item 3

    Table B
    2 -- Item 2
    3 -- Item 3

    Table C
    2 -- Item 2
    3 -- Item 3


    You're doing this:
    select a.items, sum(case when b.items is null then 0 else 1 end) cnt
    from tablea a
    left join tableb b on a.id = b.id
    left join tablec c on b.id = c.id
    where c.items = "Item 3" or c.item is null
    group by a.items

    It's not 100% accurate but it's close to illustrate hte point...
    that should return Item 1 & Item 3 ... with counts 0 & 1 respectively...
    You're starting with a complete list... then moving to a partial list... left join is appropriate... it keeps the full list... in your original code, you then switch to an inner join... well, that then drops anything missing... if you keep it as a left join, it will keep missing rows... but then you have dates in your where clause... but the dates come from your thrid table... which means they could be missing (NULL) ... it just so happens, those are the rows you DO want... so you need to includde them somehow...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: SQL - Query to return sum and 0 if no match is found

    If it's still not working, then you may want to post some sample SQL that will create the tables and sample data in the tbles that we can use to replicate hte problem... w/o knowing what your data looks like or how it is in the tab le,s it's getting harder to see what the problem is.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Re: SQL - Query to return sum and 0 if no match is found

    Thank you for taking time to look into this.
    When I try to upload an Excel file here´, it tells me invalid file thing. Which format shall I upload the tables?

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,021

    Re: SQL - Query to return sum and 0 if no match is found

    As I said:
    some sample SQL that will create the tables and sample data in the tables that we can use to replicate the problem.
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Re: SQL - Query to return sum and 0 if no match is found

    Thanks again.

    Edit, now with SQL
    Last edited by Grand; Sep 30th, 2020 at 02:12 AM.

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,269

    Re: SQL - Query to return sum and 0 if no match is found

    Quote Originally Posted by Grand View Post
    Thank you for taking time to look into this.
    When I try to upload an Excel file here´, it tells me invalid file thing. Which format shall I upload the tables?
    zip it first!
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Re: SQL - Query to return sum and 0 if no match is found

    Thanks. Is the SQL code for creating temporary sample sufficient?

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,269

    Re: SQL - Query to return sum and 0 if no match is found

    On SQLite (don't have SQLServer)
    You have to test DA.CaseID if it's null

    Code:
    SELECT 
    HOC.ListItemName,
    SUM(CASE WHEN C.CaseID ISNULL THEN 0 ELSE 1 END) As TotalSum 
    FROM HOC 
    LEFT JOIN 
    (SELECT 
    DA.CaseID,
    DA.HierarchyOrder||': '||DA.Hierarchy AS DAItem,
    NG.DEVIATION_NUMBER,
    NG.COMPLETED_DATE
    FROM DA
    INNER JOIN NG
    ON
    DA.CaseID=NG.DEVIATION_NUMBER) AS C
    ON 
    HOC.ListItemName=C.DAItem  
    GROUP BY 
    HOC.ListItemName
    ORDER BY
    HOC.ListItemName
    Returns
    Code:
    0: No Control 1
    1: Lagging Control 0
    2: Sign/ Information/ Training 1
    3: 4P Training 2
    4: Procedure/ Standard 0
    5: Leading Control 0
    6: Poka Yoke 0
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Re: SQL - Query to return sum and 0 if no match is found

    Thanks a lot
    Did a bit of modification and it does what I needed:

    Code:
    SELECT 
    HOC.ListItemName,
    SUM(CASE WHEN C.CaseID IS NULL THEN 0 ELSE 1 END) As TotalSum 
    FROM DV_ListItems as HOC  
    LEFT JOIN 
    (SELECT 
    DA.CaseID,
    Cast(DA.HierarchyOrder as varchar(2)) +': ' + DA.Hierarchy AS DAItem,
    NG.DEVIATION_NUMBER,
    NG.COMPLETED_DATE
    FROM DV_CaseOverview AS DA
    INNER JOIN DV_Data_NEW As NG 
    ON
    DA.CaseID=NG.DEVIATION_NUMBER Where 
     ( NG.[Completed_DATE]  >= '2020-06-25' And NG.[Completed_DATE]  <= '2020-09-29')  ) AS C
    ON 
    HOC.ListItemName=C.DAItem  
     Where HOC.ListItemType = 'DV Hierarchy' 
    GROUP BY 
    HOC.ListItemName
    ORDER BY
    HOC.ListItemName
    Much appreciated.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    448

    Re: [RESOLVED] SQL - Query to return sum and 0 if no match is found

    Hi again
    I have spent now a few days to tune it to the full purpose but am unable to get it to work. The last bit is this: before this I had a little code that helped me to get an overview of the data that was missing values:

    Code:
    ISNULL(DA.Hierarchy,'Missing data') As Hierarchy
    in this:

    Code:
    Select 
    Distinct ISNULL(DA.Hierarchy,'Missing data') As Hierarchy, 
    DA.HierarchyOrder , 
    Sum(Case When DA.Hierarchy IS not null  then 1 else 0 End)   As Total 
    FROM [DV_Data_NEW] As NG  
    Inner Join DV_CaseOverview AS DA 
    ON NG.DEVIATION_NUMBER = DA.CaseID   
    Where   ( NG.[Completed_DATE]>= '2020-01-01' And NG.[Completed_DATE] <= '2020-10-02')  And 
    NG.Staus = 'Closed' And 
    
    Group by DA.Hierarchy, 
    DA.HierarchyOrder  
    Order by Total DESC
    that gave me this part of the data:
    Name:  2020-10-02_13-23-49.jpg
Views: 55
Size:  31.0 KB

    With the new approach, I am not sure how to get that as a part of the output
    Any help is appreciated.
    Thanks.

Posting Permissions

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



Click Here to Expand Forum to Full Width