locked
Sum Total Number of grouped column values RRS feed

  • Question

  • User82362805 posted

    The below query get the booking personnel name on specified date and also total no of booking on each booking type for every booking personnel

    select bp.BookingPersonnelName,b.BookingDate, b.BookingType,count(bp.bookingpersonnelID) as 'Total No Of Booking',Sum(p.Amount) as Amount from Booking b
    inner join BookingPersonnel bp
    on b.BookingPersonnelID=bp.BookingPersonnelID  
    inner join Payment p on b.BookingID=p.BookingID
    group by b.BookingType,b.BookingDate,bp.BookingPersonnelName,bp.BookingPersonnelID
    BookingPersonnelName Booking Date Booking Type Total No Of Booking Amount
    TAHA 2018-04-28  Booked 14 35000
    TAHA 2018-04-07 Booked 12 27000

    As you can see above I got 2 records for one booking personnel I want one record of Taha and total no of booking should be 14+12= 26 same with amount 

    How can I get this result please help

    Friday, December 21, 2018 11:37 AM

Answers

  • User77042963 posted

    You can have one row for each date:

    select bp.BookingPersonnelName,b.BookingDate, b.BookingType,
    count(bp.bookingpersonnelID) over(Partition by b.BookingType,b.BookingDate,bp.BookingPersonnelName,bp.BookingPersonnelID ) as 'Total No Of Booking'
    ,Sum(p.Amount)  over(Partition by b.BookingType,b.BookingDate,bp.BookingPersonnelName,bp.BookingPersonnelID )  as Amount from Booking b
    inner join BookingPersonnel bp
    on b.BookingPersonnelID=bp.BookingPersonnelID  
    inner join Payment p on b.BookingID=p.BookingID


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 21, 2018 2:41 PM

All replies

  • User-2082239438 posted

    The below query get the booking personnel name on specified date and also total no of booking on each booking type for every booking personnel

    select bp.BookingPersonnelName,b.BookingDate, b.BookingType,count(bp.bookingpersonnelID) as 'Total No Of Booking',Sum(p.Amount) as Amount from Booking b
    inner join BookingPersonnel bp
    on b.BookingPersonnelID=bp.BookingPersonnelID  
    inner join Payment p on b.BookingID=p.BookingID
    group by b.BookingType,b.BookingDate,bp.BookingPersonnelName,bp.BookingPersonnelID
    BookingPersonnelName Booking Date Booking Type Total No Of Booking Amount
    TAHA 2018-04-28  Booked 14 35000
    TAHA 2018-04-07 Booked 12 27000

    As you can see above I got 2 records for one booking personnel I want one record of Taha and total no of booking should be 14+12= 26 same with amount 

    How can I get this result please help

    Remove the booking date field from the select statement as well as from the group by. You will get the desired result.

    You will find the group by learning details here

    Friday, December 21, 2018 12:28 PM
  • User82362805 posted

    yrb.Yogi but I want to show booking date column in result

    Friday, December 21, 2018 12:31 PM
  • User-2082239438 posted

    yrb.Yogi but I want to show booking date column in result

    You will check that there are two different date in the booking date field. During the group by fields, if field contain the different value it will give you different result.

    Instead of booking date field, use the aggregate function on the booking field (i.e. MIN/MAX) & remove the booking date field from the group by. You will get the single result.

    Friday, December 21, 2018 12:57 PM
  • User77042963 posted

    You can have one row for each date:

    select bp.BookingPersonnelName,b.BookingDate, b.BookingType,
    count(bp.bookingpersonnelID) over(Partition by b.BookingType,b.BookingDate,bp.BookingPersonnelName,bp.BookingPersonnelID ) as 'Total No Of Booking'
    ,Sum(p.Amount)  over(Partition by b.BookingType,b.BookingDate,bp.BookingPersonnelName,bp.BookingPersonnelID )  as Amount from Booking b
    inner join BookingPersonnel bp
    on b.BookingPersonnelID=bp.BookingPersonnelID  
    inner join Payment p on b.BookingID=p.BookingID


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 21, 2018 2:41 PM
  • User-893317190 posted

    Hi AfaqRajput,

    If you want to preserve booking date and still have the  total no of booking and amount, you could  also use subquery.

    Below is my sql.

    with temp as (  -- store the resultset of your first query into temp
    
    select bp.BookingPersonnelID, bp.BookingPersonnelName,b.BookingDate, b.BookingType,count(bp.bookingpersonnelID) as 'Total No Of Booking',Sum(p.Amount) as Amount from Booking b
    inner join BookingPersonnel bp
    on b.BookingPersonnelID=bp.BookingPersonnelID  
    inner join Payment p on b.BookingID=p.BookingID
    group by b.BookingType,b.BookingDate,bp.BookingPersonnelName,bp.BookingPersonnelID
    
    
    ) 
    select  bookingpersonnelName,BookingDate,BookingType,
     (select sum([Total No Of Booking]) from temp t where t.BookingPersonnelId=temp.BookingPersonnelId) as 'Total No Of Booking' , --sum Total booking of personnel using sub query
    (select sum(Amount) from temp t  where t.BookingPersonnelId=temp.BookingPersonnelId) -- sum amount of personnel  using subquery
     as Amount from temp 

    Here, to make it clear , I use cte , you could refer to  https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017 to learn about cte, it could preserve the result of one query and use the result in the following query.

    The result of the query in cte.

    select bp.BookingPersonnelID, bp.BookingPersonnelName,b.BookingDate, b.BookingType,count(bp.bookingpersonnelID) as 'Total No Of Booking',Sum(p.Amount) as Amount from Booking b
    inner join BookingPersonnel bp
    on b.BookingPersonnelID=bp.BookingPersonnelID  
    inner join Payment p on b.BookingID=p.BookingID
    group by b.BookingType,b.BookingDate,bp.BookingPersonnelName,bp.BookingPersonnelID
    
    
    
    1	TAHA	2018-04-07 00:00:00.000	booked	1	27000
    1	TAHA	2018-04-28 00:00:00.000	booked	1	35000

    The result of the total query.

    TAHA	2018-04-07 00:00:00.000	booked	2	62000
    TAHA	2018-04-28 00:00:00.000	booked	2	62000

    Best regards,

    Ackerly Xu

    Monday, December 24, 2018 4:15 AM