CODEWITHSUNDEEP
sqlsasproc-sql
Rods2292
Rods2292

Reputation: 675

Summarizing in SQL

I'm quite new to SQL and I'm trying to summary a table using it on SAS software.

Here is the table I have to summary:

policy_number   item 
1234              1
1234              2
1234              3
567               1
89                1
90                1
90                2

Here is the result I need:

policy_number       item   max_item
    1234              1       3
    1234              2       3
    1234              3       3
    567               1       1
    89                1       1
    90                1       2
    90                2       2

And here is my code:

proc sql;
create table example
as select 
policy_number,
item, 
max(item) as max_item
from table1
group by policy_number, item;
quit;

And it gives this result:

policy_number       item   max_item
        1234              1       1
        1234              2       1
        1234              3       3
        567               1       1
        89                1       1
        90                1       1
        90                2       2

What I'm doing wrong? Can someone help me to fix my code?

Upvotes: 0

Views: 77

Answers (4)

Moin Saiyed
Moin Saiyed

Reputation: 1

SELECT t.policy_number,t.item , max(item ) over(partition by t.policy_number) as max

FROM dbo.table1 t

ORDER BY t.policy_number

Upvotes: 0

Adil Aliyev
Adil Aliyev

Reputation: 1169

You can do it by JOINing the table to itself.

Below is simple example for that purpose:

SELECT I.policy_number, I.item, J.mx FROM example I
LEFT JOIN
(SELECT
        policy_number, max(item) AS mx
FROM example
GROUP BY policy_number) J
ON J.policy_number=I.policy_number

But depending on purpose this can be efficient or non-efficient.

Upvotes: 0

FirstCall
FirstCall

Reputation: 84

Try to think of it this way. Your table looks like this.

policy_number     item 
1234              1
1234              2
1234              3
567               1
89                1
90                1
90                2

First, the goal is to find the max item per policy, which can be done like so

SELECT policy_number, MAX(item) max_item
FROM table1
GROUP BY policy_number

This gives you the following result.

policy_number     max_item 
1234              3
567               1
89                1
90                2

The next step is to merge these together, which you can do with a sub query and a join.

SELECT table1.policy_number, item, max_item
FROM table1
JOIN (
    SELECT policy_number, MAX(item) max_item
    FROM table1
    GROUP BY policy_number
) SubQ ON SubQ.policy_number = table1.policy_number

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Hmmm. I would expect this to do what you want:

proc sql;
    create table example as
        select policy_number, item, max(item) as max_item
        from table1
        group by policy_number;
quit;

This is non-standard SQL. But in proc SQL, it should remerge the maximum for the third column.

I should add that this version is another way to do what you want:

proc sql;
    create table example as
        select t1.policy_number, t1.item, tt1.max_item
        from table1 t1 join
             (select policy_number, max(item) as max_item
              from table1
              group by policy_number
             ) tt1
             on t1.policy_number = tt1.policy_number;
quit;

Upvotes: 3

Related Questions