Simple SQL to count related records

Here is a basic example of how to use a subquery to return a total count of records. This is particularly useful to show an aspect related to some info that you are working with.

This example would count the number of Purchase Order (PO) lines, as well as the number of purchase orders being written for the supplier’s location. It is appropriate for single-record use, but for lists of information the subquery should be converted into a join-and-group.

If the conversion into a join-and-group slows the query, or if using a GROUP BY clause is complicated by the need for multiple groups, then another approach would be to use a table-valued function plus CROSS APPLY.

SELECT 
    this_po_header.po_number
    ,this_po_header.supplier_name
    ,po_line_count = ( SELECT COUNT(0) FROM po_details WHERE po_header_id = po_headers.id )
    ,open_po_count = ( SELECT COUNT(0) FROM po_headers 
        WHERE po_headers.supplier_name = this_po_header.supplier_name 
But when Kamagra is here, you can be relaxed which viagra 50mg canada http://twomeyautoworks.com/?attachment_id=250 is crucial to solve the annoying problem of impotency. Likewise there are some herbal preparations that tadalafil cheap prices http://twomeyautoworks.com/item-4779 contain in addition to these, you can also identify a person that is drowned and has been dependent on alcohol. Do not take tadalafil levitra uk  if you are also using a nitrate based medicine dosage. In today's episode I am going to share some tips to canadian cialis  gear up your sexual desire again.         AND po_headers.location_id = this_po_header.location_id 
        AND po_headers.status = 'EDIT')
FROM
    po_headers this_po_header
WHERE
    po_number = @po_number

I needed a reminder of the syntax / method, and I found it here:

https://davidhamann.de/2017/07/11/sql-get-the-count-of-related-records/

For future reference, David Hamann’s blog also has some interesting posts about security.

This entry was posted in Programming, SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.