logo
down
shadow

Count records only from left side of a LEFT JOIN


Count records only from left side of a LEFT JOIN

By : Hulkee
Date : November 22 2020, 03:01 PM
fixed the issue. Will look into that further I'm building an Access query with a LEFT JOIN that, among other things, counts the number of unique sampleIDs present in the left table of the JOIN, and counts the aggregate number of specimens (bugs) present in the right table of the JOIN, both for a given group of samples (TripID). Here's the pertinent chunk of SQL code: , just take the aggregate sum first on t2, then join with t2 like this:
code :
SELECT t1.TripID, COUNT(t1.SampleID) AS Samples, SUM(t3.Bugs) as Bugs
FROM tbl_Sample AS t1
LEFT Join (
  SELECT t2.SampleID, SUM(t2.C1 + t2.C2) as Bugs
  FROM tbl_Bugs as t2
  GROUP BY SampleID) AS t3 ON t1.SampleID = t3.SampleID
GROUP BY t1.TripID


Share : facebook icon twitter icon
MySQL LEFT JOIN SELECT not selecting all the left side records?

MySQL LEFT JOIN SELECT not selecting all the left side records?


By : Glenn
Date : March 29 2020, 07:55 AM
This might help you You need to GROUP BY d.dateID. In two of your cases, s.DateID is NULL (LEFT JOIN) and these are combined together.
I think you will also find that this is invalid (ANSI) SQL, because d.date is not part of a GROUP BY or the result of an aggregate operation, and should not be able to be SELECTed.
How do you select multiple records using AND on the left side of a JOIN

How do you select multiple records using AND on the left side of a JOIN


By : user2907061
Date : March 29 2020, 07:55 AM
help you fix your problem I have a simple requirement, but can't get the SELECT statement right, so that it will deliver the desired result.
code :
SELECT  *
FROM    person p
WHERE   (
        SELECT  COUNT(DISTINCT licence)
        FROM    licences l
        WHERE   l.client_id = p.id
                AND l.licence IN ('white', 'blue')
        ) = 2
SELECT  DISTINCT p.*
FROM    person p
JOIN    licences l
ON      l.client_id = p.id
        AND l.licence IN ('white', 'blue')
GROUP BY
        p.id
HAVING  COUNT(DISTINCT licence) = 2
Joining multiple records on the right side of a join to 1 record on the left side

Joining multiple records on the right side of a join to 1 record on the left side


By : Ashish Dev
Date : March 29 2020, 07:55 AM
I wish did fix the issue. One way you could do this without changing your query entirely is separate the Click sums and Applicant sums into two separate queries, UNION them together and then group/sum them again.
Something like this (pseudo code):
code :
SELECT
        Source
        ,SUM([Organic Clicks])
        ,.....
FROM
(
SELECT   g.name AS [Source]
        ,SUM(CASE WHEN v.cost = 0 THEN 1 ELSE 0 END) AS [Organic Clicks]
        ,SUM(CASE WHEN v.cost <> 0 THEN 1 ELSE 0 END) AS [Paid Clicks]
        ,COUNT(v.id) AS [Total Clicks]
        ,0 AS [Organic Applicants]
        ,0 AS [Paid Applicants]
        ,0 AS [Total Applicants]
        ,SUM(v.cost/100.0) AS [Spend]
FROM
        ClickOnlyTables

UNION ALL

SELECT   g.name AS [Source]
        ,0 AS [Organic Clicks]
        ,0 AS [Paid Clicks]
        ,0 AS [Total Clicks]
        ,SUM(CASE WHEN a.applicant = 1 AND v.cost = 0 THEN 1 ELSE 0 END) AS [Organic Applicants]
        ,SUM(CASE WHEN a.applicant = 1 AND v.cost <> 0 THEN 1 ELSE 0 END) AS [Paid Applicants]
        ,SUM(CASE WHEN a.applicant = 1 THEN 1 ELSE 0 END) AS [Total Applicants]
        ,0 AS [Spend]
FROM
        ApplicantTables
)
GROUP BY
        Source
LEFT JOIN does not return all the records from the left side table

LEFT JOIN does not return all the records from the left side table


By : user2335853
Date : March 29 2020, 07:55 AM
Any of those help The problem could be that you are filtering on the joined table using the where condition which will filter also the department services which don"t have a match in the join, move the filtering in the join and leave only the filters on d in the where clause:
code :
SELECT d.mt_code,
   d.dep_name,
   d.service_name,
   COUNT(t.id)
FROM DepartmentService AS d
LEFT JOIN tbl_outgoing AS t 
  ON d.mt_code = t.depCode 
    AND t.smsc = "mobitelMT"
    AND t.sendDate BETWEEN '2014-07-01' AND '2014-07-02'
WHERE d.service_type = 'MT'
GROUP BY d.mt_code
states
 ____ _________ 
| id | state   |
|  1 | Germany |
|  2 | Italy   |
|  3 | Sweden  |
|____|_________|

cities

 ____ ________ ___________ ____________
| id | city   | state_fk  | population |
|  1 | Berlin |        1  |         10 |
|  2 | Milan  |        2  |          5 |
|____|________|___________|____________|
SELECT s.id, s.state, c.population, c.city
FROM states s
LEFT JOIN cities c
ON c.state_fk = s.id
WHERE c.population < 10
 ____ _________ ____________ ________
| id | state   | population | city   |
|  1 | Germany |         10 | Berlin |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|
 ____ _________ ____________ ________
| id | state   | population | city   |
|  2 | Italy   |          5 | Milan  |
|____|_________|____________|________|
WHERE (c.population < 10 OR IS NULL c.population)
 ____ _________ ____________ ________
| id | state   | population | city   |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|
SELECT s.id, s.state, c.population, c.city
FROM states s
LEFT JOIN cities c
ON c.state_fk = s.id
  AND c.population < 10
 ____ ________ ___________ ____________
| id | city   | state_fk  | population |
|  2 | Milan  |        2  |          5 |
|____|________|___________|____________|
 ____ _________ ____________ ________
| id | state   | population | city   |
|  1 | Germany |       NULL | NULL   |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|
How do I count the number of records with no match on the right side of a Linq left join?

How do I count the number of records with no match on the right side of a Linq left join?


By : Jahangir alom Pial
Date : March 29 2020, 07:55 AM
will help you Your query is giving you at least one item for each item in your csvTable. The data you want for unassignedNumbers would be exactly one value for the entire data set. So you'd have to do a completely separate query to do that and then just assign that value directly into the results of your query if desired.
code :
int allUnassignedNumbers = 
    (from  row in csvTable.AsEnumerable()
    join d in employeeList 
    on row.Field<string>("Phone number") equals d.Mobile into t 
    from rt in t.DefaultIfEmpty()
    where rt == null
    select 1).Count();

var billInfo = 
    from  row in csvTable.AsEnumerable()
    join d in employeeList 
    on row.Field<string>("Phone number") equals d.Mobile into t 
    from rt in t.DefaultIfEmpty()
    select new
    {
        empNo = rt == null ? null : rt.EmpNo,
        totalRows = csvTable.Rows.Count,
        unassignedNumbers = allUnassignedNumbers,
        name = row.Field<string>("User name"),
        mobile = row.Field<string>("Phone number")
    };
Related Posts Related Posts :
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org