SQL - Group by with Left Join

SQL - Group By with Left Join

It's a JOIN not a NULL problem: your filter is changing the OUTER to an INNER JOIN. This means you only get COUNT where you have rows in CallTable (B) rather than the OUTER JOIN you wanted.

SELECT A.name, COUNT(B.call_id) AS 'outgoing call count' 
FROM
   EmployeeTable A 
   LEFT JOIN
   (
   SELECT call_from_name, call_id FROM CallTable
   WHERE call_type LIKE 'outgoing' 
     AND voice_mail = '0'
     AND /* other CallTable filters */
   ) B
   ON A.name = B.call_from_name
WHERE
     /* only EmployeeTable A filters */
GROUP BY A.name 

Edit: after your comment elsewhere, all your filters on B must be in the derived table, not in the outer where.

left join and group by

You can simply do a left join to get the location_id and corresponding number of rows in task table like this:

select l.id, count(t.location_id) times
from location l
left join task t
on l.id = t.location_id
group by l.id
order by max(t.create_date) desc;

If location_id is unique (probably PK) in your location table and you want to select all or more columns from that table, you can just put them in both the select and group by clause of the query.

Left Join not work with GROUP BY and WHERE clause

I wish other SQL Server questions were so clear - a minimal script that reproduces the problem, something others can easily copy and test, and a clear question, with an easy answer.

A LEFT JOIN would return a NULL for t2.Year which will be eliminated by the WHERE t2.Year=2011 clause.

If you executed just :

select t1.[Text],t2.Year
from @Tbl1 t1 left join @Tbl2 t2 
    on t1.Value = t2.[Value] and t2.Year=2011

You'd get

Text    Year
Val 1   2011
Val 1   2011
Val 1   2011
Val 2   2011
Val 3   2011
Val 4   NULL

and WHERE t2.Year=2011 would eliminate the last row.

To avoid this, you can either allow null years, or move filtering somewhere else.

IS NULL

You could allow nulls in the WHERE clause with IS NULL:

select t1.[Text],count(*)
from @Tbl1 t1 left join @Tbl2 t2 
    on t1.Value = t2.[Value] and t2.Year=2011
where t2.Year=2011 or t2.year is null
group by t1.[Text]
order by t1.Text

This produces:

Text    Count
Val 1   3
Val 2   1
Val 3   1
Val 4   1

That's what the desired response is but I'm not sure val 4 should produce 1 as there are no related rows. Count(id) would produce 0:

Text    Count
Val 1   3
Val 2   1
Val 3   1
Val 4   0

Subquery

You can use a subquery that produces only the T2 appropriate T2 rows, eg:

select t1.[Text], count(*) as 'Count'
from @Tbl1 t1 left join 
    ( select Value 
      from @Tbl2 
      where Year=2011) t2 
    on t1.Value = t2.[Value] 
group by t1.[Text]
order by t1.Text

Cross Apply

Another option would be to use CROSS APPLY to calculate the count of matching T2 rows:

select t1.[Text], Cnt as Count
from @Tbl1 t1 cross apply 
    ( select count(*) as Cnt
      from @Tbl2 t2
      where Year=2011 and t1.Value = t2.[Value] ) t2
order by t1.text

This returns :

Text    Count
Val 1   3
Val 2   1
Val 3   1
Val 4   0

The CROSS APPLY function calculates COUNT(*) for the T2 rows that have a Value equal to t1.Value and returns just that count.

LEFT JOIN with GROUP BY with WHERE clause and also with OR in sql

SELECT distinct t1.TID, t3.name, cntt1.count from taxonomy_index t1 
inner join 
( 
SELECT count(*) as 'count', t1.tid 
FROM taxonomy_index t1 
GROUP BY t1.tid HAVING COUNT(*) >3 
) cntt1 on t1.tid = cntt1.tid 
inner join node t2 on t1.nid = t2.nid and (type='book' OR type='student' OR 
type='teacher' OR type='class') 
inner join taxonomy_term_data t3 on t1.tid = t3.tid

How to GROUP BY in left join

Showing one row per patient - and his/her last visit date:

SELECT 
    Patient_Master.PID, 
    Patient_Master.Patient_ID,
    Patient_Master.Patient_Name,
    Patient_Master.Sex,
    Patient_Master.Patients_Birth_Date,
    Patient_Last_Visit.Visit_Date 
FROM 
    Patient_Master 
  LEFT JOIN
    ( SELECT 
          PID,
          MAX(Visit_Date) AS Visit_Date
      FROM
          Patient_Visit
      GROUP BY
          PID 
    ) AS Patient_Last_Visit
        ON Patient_Master.PID = Patient_Last_Visit.PID 
ORDER BY 
    Patient_Master.Patient_Name

Group by and left join in BigQuery

You should move the where statement in the join clause like this:

#standardSQL
select A.col1, A.col2, A.col3, sum(B.col3) as col4
from `Table 1` as A
left join `Table 2` as B
on A.col1 = B.col1
and B.col2 > A.col2 
and B.col2 <= A.col2+7
group by 1,2,3

Left Join two tables & group by a column into an array

The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. It does not add the separator at the end of the result string.

Use this snippet for MySql:

SELECT t1.id,t1.label,IFNULL(CONCAT('[',GROUP_CONCAT(domain),']'),'[]')  as domains
 FROM websites t1 
 left join domains t2 
 on t1.id=t2.websiteId 
 group by t1.id,t1.label

Output:

id  label           domains
1   my.website.com  [domain1.info,domain2.io]
2   new.app.gov.uk  []

Use this snippet for SQL-Server :

SELECT t1.id,t1.label,isnull('['+STRING_AGG(domain,',')+']','[]')
  FROM websites t1
  left join 
  domains t2
  on t1.id=t2.websiteId
  group by t1.id,t1.label

Output:

id  label           domains
1   my.website.com  [domain1.info,domain2.io]
2   new.app.gov.uk  []

How to combine MYSQL Left Join and Group By for another Table

You seem to want a left join and aggregation. Your question is unclear about how you want to compute the status when a user has more than one result, so I assumed you want the maximum value:

select
    u.name,
    u.active,
    case max(r.status)
        when 1 then 'awaiting'
        when 2 then 'passed'
        when 3 then 'failed'
        else 'unavailable'
    end as status
from users u
left join result r on r.user_id = u.id and r.identity = 1
where u.active = 1
group by u.id, u.name, u.active 

Note that user is a language keyword in MySQL (as well as in most other databases), hence a poor choice for a table name. I renamed it to users inthe query.

LEFT JOIN 3 tables with GROUP BY and SUM

I think you can try this -

select tu.userid,
       tu.UserName,
       tdl.ProductKey,
       tp.ProductName,
       sum(tdl.Counter) as Total
  from tblusers tu
  left join tbldailylog tdl
    on tu.userid = tdl.userid
  left join tblproducts tp
    on tu.userid = tp.userid
 group by tu.userid, tu.UserName, tdl.ProductKey, tp.ProductName
 order by sum(tdl.counter) desc

---

Related Topics