Calculate Margin - Group 1

[wexas45]

Hi, hope every one is doing well. I am new to sql queries.
I need to calculate 'MARGIN', which is 'GrossProfit'/[price]*100 and then display the reuslt in the query. Can someone please help me !

SELECT
[Booking Product],
YEAR([Booking Conf Date]) AS 'BookingYear',
COUNT(DISTINCT [Booking Ref]) AS TotalBookings,
CONVERT (DECIMAL(17,2),(SUM(GBPCost))) AS Cost,
CONVERT (DECIMAL(17,2),(SUM(price))) AS Price,
CONVERT (DECIMAL(17,2),(SUM([Price]-[GBPCost]))) AS 'GrossProfit',
([Booked Adults]+[Booked Children]+[Booked Infants])AS 'PAX',




FROM
[A]

WHERE
BookingStatus = 'CONFIRMED'

GROUP BY
[GBPCost],[Price],[Booking Product],[Booking Conf Date],[Booked Adults],[Booked Children],[Booked Infants];

 

[markros]

select *, [Gross Profit] / NULLIF(Price,0) * 100.0 as Margin from
(SELECT         [Booking Product],    
YEAR([Booking Conf Date]) AS 'BookingYear', 
       COUNT(DISTINCT [Booking Ref]) AS TotalBookings,     CONVERT (DECIMAL(17,2),(SUM(GBPCost))) AS Cost, 
    CONVERT (DECIMAL(17,2),(SUM(price))) AS Price,    CONVERT (DECIMAL(17,2),(SUM([Price]-[GBPCost]))) AS 'GrossProfit',   
 ([Booked Adults]+[Booked Children]+[Booked Infants])AS 'PAX'
            FROM             [A]
WHERE    BookingStatus = 'CONFIRMED'       
  GROUP BY     [GBPCost],[Price],[Booking Product],[Booking Conf Date],[Booked Adults],[Booked Children],[Booked Infants]) X;

PluralSight Learning Library

 

[wexas45]

Hello Markos, thanks for your reply but i am getting this erros

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Gross Profit'.

Gross Profit is not the column from the table but it's a calculated one.

Appreciate your time

Regards
WEXAS

 

[markros]

I understand that, that's why I used a derived table with your original select and added extra field in the outer select.

Did you use my suggestion as I wrote it or did you alter it in some way?

PluralSight Learning Library

 

[wexas45]

I used your suggestion

 

[markros]

I think I see now - in your code you named the column GrossProfit (no space) and I used a space. Either add a space in inner query or remove it in the outer query.

PluralSight Learning Library

 

[wexas45]

Thanks it works perfectly fine

 

[wexas45]

Hi, Question in the code you have written X at the end. What is the purpose of that and one more question some of the results are showing value as null how can i display 0.00 ?

Thanks in advance

 

[markros]

1. I used X as an alias for derived table (because I'm lazy I like to use X here). You can give it more meaningful alias - the syntax requires the alias to be used

2. Take a look at COALESCE and ISNULL functions in BOL.

PluralSight Learning Library

 

[R7Dave]

Hello

I would like to return the sum but I am returning individual records with "1"

select 
(count (distinct( DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear) ))
from dirstat_record_bk1yr
where
	dirstatcoverage=94
	and DirstatPolState = 37

group by 
	DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear
having sum(XdirstatWritPrem) > 0 and
	sum(XdirstatWritPrem) < 25000

this returns 9 rows containg the "1"

what I want is 1 record containing the 9 (the sum)

Thanks in advance
Dave

 

[markros]

I haven't re-read the whole thread, so this is based on your last query

select count(*) as TotalCount from (select 
(count (distinct( DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear) ))
from dirstat_record_bk1yr
where
    dirstatcoverage=94
    and DirstatPolState = 37

group by 
    DirstatPolState+DirstatPolAcctNbr+PolSerialNbr+XdirstatPolicyYear
having sum(XdirstatWritPrem) > 0 and
    sum(XdirstatWritPrem) < 25000 ) Z

PluralSight Learning Library