I have two tables. One of them contains customer_id and gender (table named customers) other one is contains customer_id and order_id(table named sales). I want to prove or disprove the hyphothesis that males have higher sale frquency.

I calculated M and F order frequency in two seperate rows like in below. But I want to return TRUE or FALSE.

gender  frequency
F       34
M       10

CodePudding user response：

Just a guess

with cte as ( 
             -- Your Query -- 
            )
Select Results = case when max( case when gender='M' then [frequency] end)
                           >
                           max( case when gender='F' then [frequency] end)
                       then 'true' else 'false' end
 From  cte

Results

Results
false

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (gender CHAR(1), frequency INT);
INSERT @tbl (gender, frequency) VALUES
('F', 34),
('M', 10);
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT * 
        , MAX(frequency) OVER (ORDER BY gender) AS max_frequency
    FROM @tbl
)
SELECT rs.gender
    , result = IIF(rs.max_frequency = rs.frequency, 'true', 'false')
FROM rs;

Output