获取总数 [英] Get Total count

问题描述

我想合并两列(发送方和接收方)并获取交易类型计数，然后使用Sender_Receiver主ID合并另一张表.

I want to merge two columns(Sender and Receiver) and get the Transaction Type count then merge another table with using Sender_Receiver primary id.

Sender      Receiver  Type  Amount  Date
773787639   777611388   1   300    2/1/2019
773631898   776806843   4   450    8/20/2019
773761571   777019819   6   369    2/11/2019
774295511   777084440   34  1000   1/22/2019
774263079   776816905   45  678    6/27/2019
774386894   777202863   12  2678   2/10/2019
773671537   777545555   14  38934  9/29/2019
774288117   777035194   18  21     4/22/2019
774242382   777132939   21  1275    9/30/2019
774144715   777049859   30  6309    7/4/2019
773911674   776938987   10  3528    5/1/2019
773397863   777548054   15  35892   7/6/2019
776816905   772345091   6   1234    7/7/2019
777035194   775623065   4   453454  7/20/2019

第二张表

Mobile_number     Age
773787639         34
773787632         23
774288117         65

我试图得到像这样的桌子

I am try to get like this kind of table

Sender/Receiver    Type_1    Type_4    Type_12...... Type_45   Age
773787639            3          2         0            0       23
773631898            1          0         1            2       56
773397863            2          2         0            0       65
772345091            1          1         0            3       32

推荐答案

好的，我已经看过您的老问题，您只需要在sub-query中的inner join如下:

Ok, I have seen your old question and you just need inner join in sub-query as following:

SELECT
    SenderReceiver,
    COUNT(CASE WHEN Type = 1 THEN 1 END) AS Type_1,
    COUNT(CASE WHEN Type = 2 THEN 1 END) AS Type_2,
    COUNT(CASE WHEN Type = 3 THEN 1 END) AS Type_3,
    ...
    COUNT(CASE WHEN Type = 45 THEN 1 END) AS Type_45,
    Age -- changes here
FROM
(   SELECT sr.SenderReceiver, sr.Type, st.Age from -- changes here
    (SELECT Sender AS SenderReceiver, Type FROM yourTable
    UNION ALL
    SELECT Receiver, Type FROM yourTable) sr
    join <second_table> st on st.Mobile_number = sr.SenderReceiver -- changes here
) t
GROUP BY
    SenderReceiver,
    Age; -- changes here

在上一个查询中所做的更改带有注释-- changes here.

Changes done in your previous query are marked with comments -- changes here.

请用表的原始名称替换<second_table>的名称.

Please replace the name of the <second_table> with the original name of the table.

干杯！

这篇关于获取总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！