TSQL Help to Summary Results
-
Hi
This may be discussed already. But, dunno how to call this type to search in this forum.
I have a query to return result as follows:
CusNoAmount
CUS032 945,360.07
CUS027 294,153.82
CUS020 155,843.75
CUS024 146,002.23
CUS030 134,750.89
CUS029 134,236.61
CUS025 127,230.35
CUS026 118,955.36
CUS021 97,080.81
CUS031 96,558.49
CUS028 78,380.36
CUS022 50,704.92
CUS023 11,409.38
I want to now write a query to summarise to see how many customers are with
above 750,000
between 500,000 and 750,000
between 250,000 and 499,999
between 75,000 and 249,999
below 75,000
Please help
TIA
-
This is one way to do it:
DECLARE @Example TABLE
(
CusNo char(6) PRIMARY KEY,
Amount money NOT NULL
)
INSERT @Example
(CusNo, Amount)
VALUES
('CUS032', 945360.07),
('CUS027', 294153.82),
('CUS020', 155843.75),
('CUS024', 146002.23),
('CUS030', 134750.89),
('CUS029', 134236.61),
('CUS025', 127230.35),
('CUS026', 118955.36),
('CUS021', 97080.81),
('CUS031', 96558.49),
('CUS028', 78380.36),
('CUS022', 50704.92),
('CUS023', 11409.38)
SELECT
ISNULL(SUM(CASE WHEN e.Amount < 75000 THEN 1 END), 0) AS [< 75000],
ISNULL(SUM(CASE WHEN e.Amount >= 75000 AND e.Amount < 250000 THEN 1 END), 0) AS [75000-250000],
ISNULL(SUM(CASE WHEN e.Amount >= 250000 AND e.Amount < 500000 THEN 1 END), 0) AS [250000-500000],
ISNULL(SUM(CASE WHEN e.Amount >= 500000 AND e.Amount < 750000 THEN 1 END), 0) AS [500000-7500000],
ISNULL(SUM(CASE WHEN e.Amount > 750000 THEN 1 END), 0) AS [>750000]
FROM @Example AS e
Output:
-
Second option:
DECLARE @Example TABLE
(
CusNo char(6) PRIMARY KEY,
Amount money NOT NULL
)
INSERT @Example
(CusNo, Amount)
VALUES
('CUS032', 945360.07),
('CUS027', 294153.82),
('CUS020', 155843.75),
('CUS024', 146002.23),
('CUS030', 134750.89),
('CUS029', 134236.61),
('CUS025', 127230.35),
('CUS026', 118955.36),
('CUS021', 97080.81),
('CUS031', 96558.49),
('CUS028', 78380.36),
('CUS022', 50704.92),
('CUS023', 11409.38)
DECLARE @Ranges TABLE
(
lower_bound money NULL UNIQUE,
upper_bound money NULL UNIQUE
)
INSERT @Ranges
(lower_bound, upper_bound)
VALUES
(NULL, 75000),
(75000, 250000),
(250000, 500000),
(500000, 750000),
(750000, NULL)
SELECT
r.lower_bound, r.upper_bound, COUNT_BIG(e.CusNo)
FROM @Ranges AS r
LEFT JOIN @Example AS e ON
(e.Amount >= r.lower_bound OR r.lower_bound IS NULL)
AND (e.Amount < r.upper_bound OR r.upper_bound IS NULL)
GROUP BY
r.lower_bound,
r.upper_bound
Output:
-
Thank you for the reply. Were really useful to create my version like
SELECT T.CusRange,COUNT(*) AS NoOfCustomers
FROM (
SELECT CASE
WHEN Amount > 10000000 THEN '0: Over 10M'
WHEN Amount BETWEEN 5000000 and 10000000 THEN '1: 5M TO 10M'
WHEN Amount BETWEEN 3000000 and 4999999 THEN '2: 3M TO 5M'
WHEN Amount BETWEEN 2000000 and 3999999 THEN '3: 2M TO 3M'
WHEN Amount BETWEEN 1500000 and 1999999 THEN '4: 1.5M TO 2M'
WHEN Amount BETWEEN 1000000 and 1499999 THEN '5: 1M TO 1.5M'
WHEN Amount BETWEEN 900000 and 999999 THEN '6: 900K TO 1M'
WHEN Amount BETWEEN 800000 and 899999 THEN '7: 900K TO 900K'
WHEN Amount BETWEEN 700000 and 799999 THEN '8: 700K TO 800K'
WHEN Amount BETWEEN 600000 and 699999 THEN '9: 600K TO 700K'
WHEN Amount BETWEEN 500000 and 599999 THEN '90: 500K TO 600K'
WHEN Amount BETWEEN 400000 and 499999 THEN '91: 400K TO 500K'
WHEN Amount BETWEEN 300000 and 399999 THEN '92: 300K TO 400K'
WHEN Amount BETWEEN 200000 and 299999 THEN '93: 200K TO 300K'
WHEN Amount BETWEEN 100000 and 199999 THEN '94: 100K TO 200K'
ELSE 'Below 100K'
END AS CusRange
FROM MonthlySales
) AS T
GROUP BY T.CusRange
ORDER BY T.CusRange
Had some trouble in sorting them and added the numbers as prefix "1:" to solve that.
-
You can ORDER BY a CASE expression too:
SELECT
CusRange,
COUNT_BIG(*) AS NoOfCustomers
FROM
(
SELECT
CASE
WHEN e.Amount < 75000 THEN '< 75000'
WHEN e.Amount >= 75000 AND e.Amount < 250000 THEN '75000-250000'
WHEN e.Amount >= 250000 AND e.Amount < 500000 THEN '250000-500000'
WHEN e.Amount >= 500000 AND e.Amount < 750000 THEN '500000-7500000'
WHEN e.Amount > 750000 THEN '>750000'
END
FROM @Example AS e
) AS r (CusRange)
GROUP BY
r.CusRange
ORDER BY
CASE r.CusRange
WHEN '< 75000' THEN 0
WHEN '75000-250000' THEN 1
WHEN '250000-500000' THEN 2
WHEN '500000-7500000' THEN 3
WHEN '>750000' THEN 4
END
-
My GOD :w00t:
That was new to me. Thank you very much for ORDER BY hint
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply