How do I count the frequency of an item in a new column without changing the structure of t…

I am having the following problem. I have to count the number of items for a table as a new column. Without changing the out put apart from the extra column.

As it is quite complicated i will try to provide a good example to explain what I mean:

The input table:

BS         item_id     price
-------------------------------
BS1          3        10
BS1          5        12
BS3          6        15
BS3          7        18
BS4          8         5
BS1          9        10

The count should add the column Amount. The amount is based on BS.

BS         item_id     price      Amount
    -------------------------------
    BS1          3        10        3
    BS1          5        12        3
    BS3          6        15        2
    BS3          7        18        2
    BS4          5        12        1
    BS1          8        10        3

I would like to know if it is possible to use count with a subquery in order to get the amount like i showed above.

When I use count like below in the code, i get for every BS item the value 1

SELECT COUNT(bsc_bsc_id)AS Amount,
                  katg_katg_id,
                  ex_wert
from (
        SELECT bs_bs_id,
               katg_katg_id,
               jahr,
               TO_CHAR (TO_DATE (a.jahr || a.monat, 'YYYYMM'), 'YYYYMM') AS monat_key,
               bsc_bsc_id,
               buchg_date,
               ex_wert,
               vp_wert
 FROM  fis_buchg_schl a

The code above is more or less pseudo code since i took big parts away because it was too long. A hint in the right direction to solve my problem would be great.

Assuming you are using SQLServer 2005 or later, try:

Select BS, item_id, price,
       count(*) over (partition by BS) Amount
from yourTable

EDIT: Should work for Oracle, too.

Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » How do I count the frequency of an item in a new column without changing the structure of t…

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录