Skip to main content
Database Administrators

Return to Answer

added 414 characters in body
Source Link
nbk
nbk
  • 8.7k
  • 6
  • 14
  • 27

You have 2 possibilities to do thatwanted inly explained how the syntax works. But as complete Query

CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)
GO

SELECTINSERT BedroomsAvailableINTO #TempListings VALUES(13, 5000, 2, 1)
INSERT INTO #TempListings VALUES(14, 6000,COUNT(listingid 2, 1)
INSERT ASINTO Count#TempListings VALUES(15, 7000, 2, 1)

INSERT INTO #TempListings VALUES(16, 8000,MIN(price 2, 2)
INSERT ASINTO MinPrice#TempListings VALUES(17, 9000, 2, 2)
INSERT INTO #TempListings VALUES(18, 10000,MAX(price 2, 2)
GO

WITH #myselect
 AS MaxPrice(SELECT 
    ,AVG(price) AS AveragePrice   BedroomsAvailable
         ,STDEV(price)ListingId as 
 StandardDeviation        ,UnitTypeId
         ,Price,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitTypeIdPrice)  
         OVER (PARTITION BY BedroomsAvailableUnitTypeId) AS MedianCont
    FROM #TempListings)
SELECT BedroomsAvailable 
    ,PERCENTILE_CONTCOUNT(0.5listingid) WITHINAS GROUPCount (ORDER 
 BY UnitTypeId  ,MIN(price) AS MinPrice 
    OVER,MAX(price) AS MaxPrice 
    ,AVG(price) AS AveragePrice
    ,STDEV(price) as StandardDeviation 
    , MIN(MedianCont) MedianCont
    ,UnitTypeId
FROM #TempListings#myselect
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId   
GO

BedroomsAvailable | Count |  MinPrice |   MaxPrice | AveragePrice | StandardDeviation | MedianCont | MedianCont | UnitTypeId
----------------: | ----: | --------: | ---------: | -----------: | ----------------: | ---------: | ---------: | ---------:
                1 |     3 | 1000.0000 |  3000.0000 |    2000.0000 |              1000 |        1.5 |        1.52000 |          1
                2 |     3 | 1000.0000 |  3000.0000 |    2000.0000 |              1000 |        1.5 |        1.52000 |          1
                21 |     36 | 1000.0000 | 3000 7000.0000 |    20004000.0000 |              1000 |        12366.543191323985 |        1.54000 |          2
                12 |     36 | 1000.0000 | 300010000.0000 |    20005500.0000 |              1000 |        13937.500393700591 |        1.54000 |          2

db<>fiddle herehere

You have 2 possibilities to do that

CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)
GO

SELECT BedroomsAvailable 
    ,COUNT(listingid) AS Count 
    ,MIN(price) AS MinPrice 
    ,MAX(price) AS MaxPrice 
    ,AVG(price) AS AveragePrice
    ,STDEV(price) as StandardDeviation
    ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitTypeId)  
    OVER (PARTITION BY BedroomsAvailable) AS MedianCont
        ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitTypeId)  
    OVER () AS MedianCont
    ,UnitTypeId
FROM #TempListings
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId
GO

BedroomsAvailable | Count |  MinPrice |  MaxPrice | AveragePrice | StandardDeviation | MedianCont | MedianCont | UnitTypeId
----------------: | ----: | --------: | --------: | -----------: | ----------------: | ---------: | ---------: | ---------:
                1 |     3 | 1000.0000 | 3000.0000 |    2000.0000 |              1000 |        1.5 |        1.5 |          1
                2 |     3 | 1000.0000 | 3000.0000 |    2000.0000 |              1000 |        1.5 |        1.5 |          1
                2 |     3 | 1000.0000 | 3000.0000 |    2000.0000 |              1000 |        1.5 |        1.5 |          2
                1 |     3 | 1000.0000 | 3000.0000 |    2000.0000 |              1000 |        1.5 |        1.5 |          2

db<>fiddle here

You wanted inly explained how the syntax works. But as complete Query

CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)

INSERT INTO #TempListings VALUES(13, 5000, 2, 1)
INSERT INTO #TempListings VALUES(14, 6000, 2, 1)
INSERT INTO #TempListings VALUES(15, 7000, 2, 1)

INSERT INTO #TempListings VALUES(16, 8000, 2, 2)
INSERT INTO #TempListings VALUES(17, 9000, 2, 2)
INSERT INTO #TempListings VALUES(18, 10000, 2, 2)
GO

WITH #myselect
 AS (SELECT 
         BedroomsAvailable
         ,ListingId  
         ,UnitTypeId
         ,Price,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price)  
         OVER (PARTITION BY UnitTypeId) AS MedianCont
    FROM #TempListings)
SELECT BedroomsAvailable 
    ,COUNT(listingid) AS Count  
    ,MIN(price) AS MinPrice 
    ,MAX(price) AS MaxPrice 
    ,AVG(price) AS AveragePrice
    ,STDEV(price) as StandardDeviation 
    , MIN(MedianCont) MedianCont
    ,UnitTypeId
FROM #myselect
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId   
GO

BedroomsAvailable | Count |  MinPrice |   MaxPrice | AveragePrice | StandardDeviation | MedianCont | UnitTypeId
----------------: | ----: | --------: | ---------: | -----------: | ----------------: | ---------: | ---------:
                1 |     3 | 1000.0000 |  3000.0000 |    2000.0000 |              1000 |       2000 |          1
                2 |     3 | 1000.0000 |  3000.0000 |    2000.0000 |              1000 |       2000 |          1
                1 |     6 | 1000.0000 |  7000.0000 |    4000.0000 |  2366.43191323985 |       4000 |          2
                2 |     6 | 1000.0000 | 10000.0000 |    5500.0000 |  3937.00393700591 |       4000 |          2

db<>fiddle here

Source Link
nbk
nbk
  • 8.7k
  • 6
  • 14
  • 27

You have 2 possibilities to do that

CREATE TABLE #TempListings
(
    ListingId int,
    Price money,
    UnitTypeId int,
    BedroomsAvailable int
)
INSERT INTO #TempListings VALUES(1, 1000, 1, 1)
INSERT INTO #TempListings VALUES(2, 2000, 1, 1)
INSERT INTO #TempListings VALUES(3, 3000, 1, 1)

INSERT INTO #TempListings VALUES(4, 1000, 1, 2)
INSERT INTO #TempListings VALUES(5, 2000, 1, 2)
INSERT INTO #TempListings VALUES(6, 3000, 1, 2)

INSERT INTO #TempListings VALUES(7, 1000, 2, 1)
INSERT INTO #TempListings VALUES(8, 2000, 2, 1)
INSERT INTO #TempListings VALUES(9, 3000, 2, 1)

INSERT INTO #TempListings VALUES(10, 1000, 2, 2)
INSERT INTO #TempListings VALUES(11, 2000, 2, 2)
INSERT INTO #TempListings VALUES(12, 3000, 2, 2)
GO

SELECT BedroomsAvailable 
    ,COUNT(listingid) AS Count 
    ,MIN(price) AS MinPrice 
    ,MAX(price) AS MaxPrice 
    ,AVG(price) AS AveragePrice
    ,STDEV(price) as StandardDeviation
    ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitTypeId)  
    OVER (PARTITION BY BedroomsAvailable) AS MedianCont
        ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UnitTypeId)  
    OVER () AS MedianCont
    ,UnitTypeId
FROM #TempListings
GROUP BY BedroomsAvailable, UnitTypeId
ORDER BY UnitTypeId
GO

BedroomsAvailable | Count |  MinPrice |  MaxPrice | AveragePrice | StandardDeviation | MedianCont | MedianCont | UnitTypeId
----------------: | ----: | --------: | --------: | -----------: | ----------------: | ---------: | ---------: | ---------:
                1 |     3 | 1000.0000 | 3000.0000 |    2000.0000 |              1000 |        1.5 |        1.5 |          1
                2 |     3 | 1000.0000 | 3000.0000 |    2000.0000 |              1000 |        1.5 |        1.5 |          1
                2 |     3 | 1000.0000 | 3000.0000 |    2000.0000 |              1000 |        1.5 |        1.5 |          2
                1 |     3 | 1000.0000 | 3000.0000 |    2000.0000 |              1000 |        1.5 |        1.5 |          2

db<>fiddle here