logo
down
shadow

Trouble With GROUP BY and JOIN on SQL


Trouble With GROUP BY and JOIN on SQL

Content Index :

Trouble With GROUP BY and JOIN on SQL
Tag : sql , By : Jody Bannon
Date : November 29 2020, 09:01 AM

around this issue I have 2 tables one with Employee names and Employee Codes, the other with the same employee codes but with the employee's arrival and departure time. One column on the latter table contains a varchar that states whether they arrived late or early and the table shows values for 3 days and 6 employees. I need to use join to show the first and last name from the first table with every column from the second table and also not show employees that have arrived late one or more times. The broken code I have now is: , I don't have the time to make up a set of test data, but try this:
SELECT DISTINCT
    EmpRegister.LastName, 
    EmpRegister.FirstName, 
    TimeSheet.* 
FROM EmpRegister 
INNER JOIN TimeSheet 
    ON EmpRegister.ID=TimeSheet.EmpID 
WHERE NOT EXISTS (
  SELECT 1
  FROM TimeSheet 
  WHERE  EmpRegister.ID = TimeSheet.EmpID
  AND Flag = 'Late'
  )

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Trouble with my SQL join/group by/cursor question


Tag : sql-server , By : kraszie
Date : March 29 2020, 07:55 AM
will be helpful for those in need I've made database design for a small CRM system. It comprises of Companies and Meetings (amongst others). , Try:
select Companies.ID, Companies.name, mts.whentime, mts.notes
from Companies
cross apply 
(
    select top(1) *
    from Meetings
    where Companies.ID = Meetings.companyID
    order by [whentime] desc
) mts
order by Companies.name asc;

Using Linq to Join,Group By and Count.... facing a trouble


Tag : chash , By : m0gb0y74
Date : March 29 2020, 07:55 AM
wish of those help You could group an anonymous class. That way you can access both p & l properties.
Looks like this :
var total = from p in db.picturedetails
            join l in db.picturelikes on p.idpictures equals l.idpictures 
            group new {p,l} by l.idpictures into g
            select new 
            {
                IdUserPic = g.First().p.iduser,
                IdPictures = g.First().p.nuditylevel,
                totalrating = g.Count()    
            }

Trouble with two table join and Group By working together


Tag : sql , By : Andrew Bailey
Date : March 29 2020, 07:55 AM
To fix this issue First, learn to use proper, explicit JOIN syntax. Second, the unaggregated columns in the SELECT need to match the GROUP BY columns. Third, use table aliases:
SELECT s.Last_Name || ', ' || s.First_Name AS "Student Name"
FROM Student s JOIN
     Grade g
     ON s.Student_Id = g.Student_Id
GROUP BY s.Last_Name || ', ' || s.First_Name
HAVING AVG(g.Numeric_Grade) > 92
ORDER BY s.Last_Name || ', ' || s.First_Name;

Having trouble with Group by and Join EF c#


Tag : chash , By : user186876
Date : March 29 2020, 07:55 AM
To fix this issue I am having trouble converting this T-SQL code into EF C# , A direct translation would look more like this:
from u in ctx.Users
join hc in ctx.HContainers on u.Hcid equals hc.Hcid
join us in ctx.UserSettings on hc.Sid equals us.Sid
where u.Created.Day == 18
group u.Userid by us.Pcname into g
let total = g.Count()
orderby total descending
select new
{
    pcname = g.Key,
    total,
}

Trouble ordering GROUP BY, ORDER BY AND JOIN


Tag : sql , By : Matthew Steed
Date : March 29 2020, 07:55 AM
I wish this helpful for you i'm having trouble ordering a query. , you could use a join
SELECT
    ATTENDANCELOG.studentpin,
    Students.FNAME,
    Students.LNAME,
    SUM(CASE WHEN status = 'YES' THEN 1 ELSE 0 END) AS number_of_yes,
    SUM(CASE WHEN status = 'NO' THEN 1 ELSE 0 END) AS number_of_no
FROM attendancelog
INNER JOIN Students ON Students.STUDENTPIN = attendancelog.StudentPin 
                         and INTERNATIONAL='YES' 
GROUP BY ATTENDANCELOG.studentpin, Students.FNAME, Students.LNAME
ORDER BY ATTENDANCELOG.studentpin
Related Posts Related QUESTIONS :
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • Spooling data to CSV truncates
  • How do views reduce code duplication?
  • SQL: Gather right hand values from a join
  • SQL Agent Logon - What is going on?
  • How do I combine two rows of same part, but add quantities?
  • How to optimize this SQL query for a rectangular region?
  • Pass data from ssrs subreport to parent
  • how to enter values in rowguid column?
  • Read multiple tables from dataset in Powershell
  • In SQL how to I exclude a record if there are more than 3 characters after a dash
  • What is the difference between check and foreign key?
  • How to return a record from function, executed by INSERT/UPDATE rule (trigger)?
  • SQL order by oldest from Unix Timestamps
  • How to convert decode function in oracle to standard big query
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com