Need assistance with T-SQL query UNION, JOINS, COUNT

Question

Looks that I am stumped with query to sum up shipments grouped by by union operator. Today I was working to retrieve total shipments (count(Distinct. U.SjipmentId) delivered by agent, driver (U.AgentCode) to particular country (U.CtryCode, U.CtryName). The last thing I would like to do is to sum all the shipments together to get the total amount of shipments. Would anyone advise how I this can be achieved in easy and simply way? Below you can find my most current query.

SELECT U.AgentCode, U.CtryCode, U.CtryName, count(distinct U.Id)
    FROM (
    select Agent.AgentCode, Addr.CtryCode, Ctry.Name, Ship.Id
    from Shipment
    LEFT JOIN RouteTab (nolock) ON RoutTbl.Cexp= Shipment.ID  
    LEFT JOIN Agent (NOLOCK) ON Agent.AgentID = RouteTbl.AgentID
    LEFT JOIN Addr (NOLOCK) ON Addr.AddrId = Shipment.AddrId
    LEFT JOIN Ctry (NOLOCK) ON Ctry.Id = Addr.Id
    WHERE RouteTbl.Bur ='GB01' AND Agent.AgentCode IS NOT NULL
    Union ALL
    select Driver.DriverCode, Addr.CtryCode, Ctry.Name, Shipment.Id
    from Shipment
    LEFT JOIN RouteTab (nolock) ON RoutTbl.Cexp= Shipment.Id
    LEFT JOIN Driver (NOLOCK) ON Driver.DriverId = RouteTbl.DriverId
    LEFT JOIN Addr (NOLOCK) ON Addr.AddrId = Shipment.AddrId
    LEFT JOIN Ctry (NOLOCK) ON Ctry.Id = Addr.Id
    WHERE RouteTbl.Bur ='GB01' AND Driver.DriverCode IS NOT NULL
    )  as U 
GROUP BY U.AgentCode, U.CtryCode, U.CtryName
ORDER BY U.AgentCode, U.CtryCode, U.CtryName

Possible duplicate of [Count number of records returned by group by](http://stackoverflow.com/questions/5146978/count-number-of-records-returned-by-group-by) — , Dec 11 '16 at 18:19
Also this one: http://stackoverflow.com/questions/12927268/sum-of-grouped-count-in-sql-query/12927333#12927333 — , Dec 11 '16 at 18:36

Anand · Answer 1 · 2016-12-11T18:46:24.703

0

Union statements need to have the exact same column names, in your code below the Union All command, try this:

select Driver.DriverCode as AgentCode, Addr.CtryCode, Ctry.Name, Shipment.Id

Also change the Ctry.Name to Ctry.Name as CtryName in both your select statements.

edited Dec 11 '16 at 18:46

answered Dec 11 '16 at 18:28

Anand

1,165
10
18

score 0 · Answer 2 · edited Dec 11 '16 at 21:06

You have the same code from your UNION. Good way to use WITH clause. In your select you don't need a UNION - use a left join and COALESCE instead.

;With r_tab AS
(
select RouteTab.AgentID, Addr.CtryCode, Ctry.Name, Ship.Id,RouteTab.DriverId
    from Shipment
    LEFT JOIN RouteTab (nolock) ON RouteTab.Cexp= Shipment.ID 
    LEFT JOIN Addr (NOLOCK) ON Addr.AddrId = Shipment.AddrId
    LEFT JOIN Ctry (NOLOCK) ON Ctry.Id = Addr.Id
    WHERE RouteTab.Bur ='GB01'
)
SELECT COALESCE(Agent.AgentCode,Driver.DriverCode) AgentCode, U.AgentCode, U.CtryCode, U.CtryName,
       count(distinct U.Id)
    FROM r_tab U
    LEFT JOIN Agent (NOLOCK) ON Agent.AgentID = U.AgentID
       AND Agent.AgentCode IS NOT NULL
    LEFT JOIN Driver (NOLOCK) ON Driver.DriverId = U.DriverId
       AND Driver.DriverCode IS NOT NULL
GROUP BY COALESCE(Agent.AgentCode,Driver.DriverCode), U.CtryCode, U.CtryName
ORDER BY U.AgentCode, U.CtryCode, U.CtryName`enter code here`

Need assistance with T-SQL query UNION, JOINS, COUNT

2 Answers2