CODEWITHSUNDEEP
sqlsql-server
adohertyd
adohertyd

Reputation: 2689

SQL Server Display total value in separate column where group by already breaks down the total

Sorry for the convoluted title!

I have a table called bookings as follows:

|Booking_id|Client_Id|Supplier_id|
----------------------------------
|    1     |    8    |    123    |
|    2     |    54   |    354    |
|    3     |    54   |    100    |
|    4     |    8    |    123    |
|    5     |    79   |    64     |
|    6     |    123  |    354    |
|    7     |    8    |    354    |
|    8     |    54   |    100    |
|    9     |    123  |    354    |
|    10    |    22   |    123    |

I have to get the % of total bookings per supplier per client

So output should be:

% of total bookings per supplier per client

|Client_id|Supplier_id|NumOfBookings|%_ofClientBookings|
--------------------------------------------------------
|    8    |    123    |      2      |         66       |
|    8    |    354    |      1      |         33       |
|    22   |    123    |      1      |         100      |
|    54   |    354    |      1      |         50       |
|    54   |    100    |      1      |         50       |
|    79   |    64     |      1      |         100      |
|    123  |    354    |      2      |         100      |

The query I have is as follows:

SELECT Client_id, COUNT(Booking_id) As NumOfBookings, Supplier_id, 
FROM Booking
GROUP BY Client_id, Supplier_id
ORDER BY Client_id asc, NumOfBookings desc

I need to GROUP_BY Supplier_id and Client_id to see the number of bookings per supplier per client, but then I can't display the % of total bookings per client in the same table because the total booking figure isn't available to me. I can't seem to solve this, any suggestions?

Upvotes: 0

Views: 60

Answers (5)

Eric
Eric

Reputation: 3257

Try this

SELECT Client_id, NumOfBookings, Supplier_id
, (CAST(NumOfBookings AS DECIMAL(5, 2)) / SUM(NumOfBookings) OVER(PARTITION BY Client_id)) * 100 AS [%_ofClientBookings]
FROM (
    SELECT Client_id, COUNT(Booking_id) As NumOfBookings, Supplier_id
    FROM Booking
    GROUP BY Client_id, Supplier_id
) a
ORDER BY Client_id asc, NumOfBookings desc

Upvotes: 1

Outman
Outman

Reputation: 3330

Try something like this:

SELECT Client_id,
COUNT(Booking_id) As NumOfBookings,
FLOOR(100*(CAST(COUNT(Booking_id) AS FLOAT)/(SELECT COUNT(Booking_id) FROM Booking bb 
WHERE bb.Client_id = b.Client_id))),
Supplier_id
FROM Booking b
GROUP BY Client_id, Supplier_id
ORDER BY Client_id asc, NumOfBookings desc

Live Demo.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

This script will help-

SELECT A.Client_Id,A.Supplier_id,A.C NumOfBookings,
CAST((A.C*1.0)/B.C*100.00 AS INT) AS Percentage
FROM 
(
    SELECT Client_Id,Supplier_id ,COUNT(Booking_id) C
    FROM your_table
    GROUP BY Client_Id,Supplier_id 

)A
INNER JOIN 
(
    SELECT Client_Id,COUNT(Booking_id) C
    FROM your_table
    GROUP BY Client_Id
)B
ON A.Client_Id = B.Client_Id

Upvotes: 1

reidh.olsen
reidh.olsen

Reputation: 111

I was able to get the desired result using derived tables, one for the number of bookings by supplier and client, and one for the total bookings by client.

Below is the code I used, you would need to replace #table with your table and ignore the inserts/create table.

IF OBJECT_ID('tempdb..#table') IS NOT  NULL
BEGIN
    DROP TABLE #table
END

CREATE TABLE #table(Booking_id int, Client_id int, supplier_id int)

insert into #table values(1,8,123)
insert into #table values(2,54,354)
insert into #table values(3,54,100)
insert into #table values(4,8,123)
insert into #table values(5,79,64)
insert into #table values(6,134,354)
insert into #table values(7,8,354)
insert into #table values(8,54,100)
insert into #table values(9,123,354)
insert into #table values(10,22,123)

SELECT NoB.Client_id,NoB.supplier_id,NoB.NumOfBookings, (NoB.NumOfBookings/(NULLIF(Tot.NumOfBookings,0) *1.0)) *100 FROM
    (
        SELECT Client_id, COUNT(Booking_id) As NumOfBookings, Supplier_id 
        FROM #table
        GROUP BY Client_id, Supplier_id

    ) NoB JOIN
    (
        SELECT Client_id, COUNT(Booking_id) As NumOfBookings 
        FROM #table
        GROUP BY Client_id

    ) Tot on Tot.Client_id=NoB.Client_id

Upvotes: 1

Saad Ahmad
Saad Ahmad

Reputation: 403

Use "with" or multiple inline views, i.e. queries in from clause. With is simpler.

With bookbyclient as (select that has total for client.  Key for result is client)
Now select like you have but join to bookbyclient.  In select list divide booking value here by the total from above to get percent

Upvotes: 1

Related Questions