logo
Tags down

shadow

Using Where clause in Group Join


By : Karem Ailsede
Date : September 15 2020, 05:00 AM
Does that help There is a straightforward one to many relation between Categories and SubCategories: every Category has zero or more SubCategories; every SubCategory belongs to exactly one Category, namely the Category that the foreign key SubCategory.CategoryId refers to.
You want to join Category and SubCategory on this foreign key. You don't want all Category-SubCategory combinations that match, you want only those where Category.CategoryModel is not equal to SubCategory.SubCategoryModel.
code :
var result = Categories.Join(SubCategories, // join tables Categories and SubCategories
    category => category.Id,                // from every category take the Id,
    subCategory => subCategory.CategoryId,  // from every subCategory take foreign key CategoryId

    (category, subCategory) => new          // when they match make one new object
    {
       // we need at least Category.CategoryModel and SubCategory.SubCategoryModel
       CategoryModel = category.CategoryModel,
       SubCategoryModel = subCategory.SubCategoryModel,

       // Select other Category properties that you plan to use:
       CategoryId = category.Id,
       ...

       // Select other SubCategory properties that you plan to use:
       ...
})
// we don't want all combinations, only those where
// CategoryModel is not equal to SubCategoryModel
.Where(joinResult => joinResult.CategoryModel != joinResult.SubCategoryModel)

// from the remaining combinations calculate the final result
.Select(joinResult => new
{
    Id = joinResult.CategoryId,
    Count1 = ... // sorry, don't know what property grpDetail does
    ...
});


Share : facebook icon twitter icon

Sum With Inner Join Group by Clause


By : Whimsical
Date : March 29 2020, 07:55 AM
like below fixes the issue The issue appears to be that your Child Table counts the MasterID twice. Try selecting the Child table as a CTE with a row_number, partitioned by MasterID to filter out duplicates:
code :
select
    CONVERT(varchar,BillDate,103) as BillDate,
    SUM(cash)as ByCash
from    (
        select *,
            row_number() over(partition by MasterID order by ChildID) dedupe
        from childdetails
        ) CD inner join MasterDetails MD on MD.ID=CD.MasterID
where
    CONVERT(varchar,BillDate,103)='22/02/2014'
    and CD.dedupe = 1
group by BillDate

Is the join necessary for the Group By clause?


By : user3141412
Date : March 29 2020, 07:55 AM
this will help You don't need the hotel table. The foreign key value in booking is fine for the comparisons, assuming that all the bookings are, indeed, for a hotel.
This should work fine, even if both HotelNo and RoomNo are needed to identify a booking and room:
code :
SELECT hotelNo, SUM(price)
FROM Room r
WHERE r.roomNo NOT IN (SELECT b.roomNo
                       FROM Booking b
                       WHERE (dateFrom <= CURRENT_DATE AND dateTo >= CURRENT_DATE) AND
                             b.hotelNo = r.hotelNo
                      )
GROUP BY hotelNo; 
SELECT hotelNo, SUM(price)
FROM Room r
WHERE NOT EXISTS (SELECT 1
                  FROM Booking b
                  WHERE (dateFrom <= CURRENT_DATE AND dateTo >= CURRENT_DATE) AND
                        b.hotelNo = r.hotelNo and
                        b.roomNo = r.roomNo
                 )
GROUP BY hotelNo;
SELECT r.hotelNo, SUM(r.price)
FROM Room r left join 
     Booking b 
     ON b.dateFrom <= CURRENT_DATE AND
        b.dateTo >= CURRENT_DATE and
        b.roomNo = r.roomNo and b.hotelNo = r.hotelNo
WHERE b.roomNo is null       
GROUP BY r.hotelNo; 

mysql query with join clause and group by clause


By : Subas Sth
Date : March 29 2020, 07:55 AM
will be helpful for those in need I want this as result , I tried like this
code :
select 
 c_name,A.c_id,
    count(c_eml) as 'ceml',COUNT(c_num) AS CNUMBER_COUNT
from #A(table 1) A
  left join #B(table_2)  B on
 A.c_id=B.c_id
group by
    c_name,A.c_id

In Join condition I want to use group by and having clause but get error? How to use group by and having clause


By : Gray Gen
Date : March 29 2020, 07:55 AM
will help you When I want to fetch data from table it_Service_ticket. The error is , I think you just want where, not having:
code :
SELECT Assignedto, COUNT(Assignedto) as TC,
       CONCAT(AVG(STATUS = 'CLOSE') * 100, '%') as SC,
       CONCAT(AVG(STATUS = 'PENDING') * 100, '%') as PC,
       SUM(TIMESTAMPDIFF(MINUTE, Request_Date, Xetr))/60 as WH ,
       (540-SUM(TIMESTAMPDIFF(MINUTE, Request_Date, Xetr)))/60 as VH,
       COUNT(Feedback_Rate)/COUNT(Assignedto)*100 as Feed_Percent,
       SUM(Feedback_Rate)/(count(Feedback_Rate)*5)*5 as AVG_Feedback
FROM it_service_ticket st INNER JOIN
     it_problem` p
     ON st.it_problem_id = p.it_problem_id INNER JOIN
     `city_master` cm
     ON st.cityid = cm.city_id
WHERE st.xetr BETWEEN '2017-01-01 00:00:00' AND '2017-03-31 12:00:00'
GROUP BY Assignedto;

How can I use an INNER JOIN in conjunction with a GROUP BY clause?


By : Hassin Ayaz
Date : March 29 2020, 07:55 AM
hope this fix your issue I have two tables, Transaction and TransactionType. Here is a sample of what the tables look like. , You should group by tt.Description
code :
SELECT COUNT(t.TransactionTypeId), tt.[Description]
FROM TransactionTable t
INNER JOIN TransactionType tt ON t.TransactionTypeId = tt.TransactionTypeId
GROUP BY tt.[Description]
ORDER BY tt.[Description]
Related Posts Related Posts :
  • How to improve sqlite write performance in C#
  • c# add delay between concurrent task executions
  • timing a mutex,unsynchronized threads, and a semaphore in C#
  • Getting "Metadata generation failed" when building C# function app in Visual Studio 2019
  • Not all code paths return a value, how do I solve this?
  • How to solve the Proxy Server 407 error in .NET.CORE
  • Why doesn't [EnableCors("*", "*", "*")] allow all requests?
  • Ef core 3.0 gives error when inserting record 'The property 'Id' on entity type 'ChannelTypeT' could not be found'
  • How can I get access to the IWebHostEnvironment from within an ASP.NET Core 3 controller?
  • I have exported my data from grivdview to excel file now I want to save it directly on desktop through code how can I do
  • creating a method to find the sum of first and last Queue members
  • Dynamic connection string database c#
  • Open XML - find and replace multiple placeholders in document template
  • Name a complex type
  • Negating a Task<bool> without await?
  • Saving XML file “on the fly” in c#
  • C# - Async method in SELECT LINQ operation
  • Entity Framework : ChangeTracker loads all my table elements
  • Access public method from another namespace in Unity
  • My LIST of customers gets populated but will not display in my Foreach loop in Server Side Blazor
  • c# delete multiple data from database at once
  • Running a method forever while running other methods
  • How to tell Application to read <runtime> from my custom app.config file instead of from default one
  • System.TypeLoadException occurred when i try to compile
  • How to create class variable name dynamically in C#
  • How to deal with a massive amount of conditions
  • How to create complex dynamic queries in EF Core 3.1 after breaking changes?
  • WCF inactivityTimeout
  • How to get the digits before some particular word using regex in c#?
  • When other controls is placed inside datagrid column it is not binding
  • Connect to a SQL Server database in WPF C# (.exe) application from any PC
  • Get instance of singleton service in startup
  • Access controls located on the main form when the child form closes
  • C# Regex to match single number among multiple numbers in a string
  • C# regex string that is not another string
  • Pass Image object as a parameter from C# to Python
  • C# winform Two timers access same variable
  • What type of collection should be returned from EFCore 3.1 in WebAPI?
  • Don't repeat return values in C#8 switch syntax
  • How to speed up this LINQ query with join?
  • What is the best way to pass between scenes?
  • How do i play an animation on a button press
  • winform will Timer.timer create an new thread?
  • How to call async lamba expression with LINQ Select?
  • How to link IObservable and IObserver in C#?
  • How to leverage dynamic programming inside of a Linq Where clause?
  • Handling SQL Server errors separately from other types of exceptions
  • Proper way to update the list of records in table using entity framework
  • chunk file into pieces
  • why I can't get txtBox2.Text and txtBox3.Text value?
  • How can I use one function, which belongs to two objects, for only one objects?
  • I want string to contain specific charachers
  • multiply chars from array in C#
  • Load file as byte array with no allocate it in memory C#
  • how to make Wait until all threads finished it's work from threadpool in c#
  • How to: Project Architecture for Unity3D or Spaghetti code?
  • How to catch expander header button click event
  • How do you return 2 variables in C#/csharp?
  • can i update the private static dateTime each time the task runs (C# - quartz-scheduler)
  • C# string.this int ' cannot be assigned to (it is read-only) REPLACE
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk