Geek Answers Handbook

Aggregating SQL results, two tables, and then summarizing the results

Using MSSQL

I have a user table and a table of products to which they are subscribed. These subscriptions are free (F) or paid (P). I joined the tables, converted the F / P value to a numerical value using the case statement, and then summed these values ​​by user ID, assuming that anyone with only free accounts will have a sum of 0, those that have at least would have one paid account of 1 or more. I got this far from the following:

SELECT t1.user_id, SUM(
    CASE
        WHEN t2.free_paid = 'P'
            THEN 1
        ELSE 0
    END ) as highest
FROM users t1 INNER JOIN accounts t2
    ON t1.user_id = t2.user_id
WHERE t2.account = 'A'
GROUP BY t1.user_id
ORDER BY t1.user_id

This gives a result like:

755   2
1259  2
2031  1
3888  0

This means that all but 3888 have at least one paid account.

-, , (3 ) , (1 ).

, . @free @paid case , , .

?

+4
3

, CTE ( ) :

;WITH CTE_UserAccounts AS (
    SELECT t1.user_id, SUM(
        CASE
            WHEN t2.free_paid = 'P'
                THEN 1
            ELSE 0
        END ) as highest
    FROM users t1 INNER JOIN accounts t2
        ON t1.user_id = t2.user_id
    WHERE t2.account = 'A'
    GROUP BY t1.user_id
)
SELECT
    COUNT(CASE WHEN highest > 0 THEN 1 END) AS [Paid],
    COUNT(CASE WHEN highest = 0 THEN 1 END) AS [Free]
FROM 
    CTE_UserAccounts;
+3
SELECT 
COUNT(DISTINCT CASE WHEN t2.free_paid = 'P' THEN t1.user_id END) as atleast_one_paid,
COUNT(DISTINCT CASE WHEN t2.free_paid <> 'P' THEN t1.user_id END) as onlyfree
FROM users t1 
INNER JOIN accounts t2 ON t1.user_id = t2.user_id
WHERE t2.account = 'A'
+1

SELECT SUM (case when highest > 0 THEN 1 else 0 END) as UsersWithPaidAccount,
SUM (case when highest = 0 THEN 1 else 0 END) as UsersWithOnlyFreeAccount
FROM (SELECT t1.user_id, SUM(
    CASE
        WHEN t2.free_paid = 'P'
            THEN 1
        ELSE 0
    END ) as highest
FROM users t1 INNER JOIN accounts t2
    ON t1.user_id = t2.user_id
WHERE t2.account = 'A'
GROUP BY t1.user_id)
as DerivedTable
+1

More articles:


All Articles