SQL Server Group by Vertical and Horizontal Data

eqqqjvef 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(77)

please consider table below (TableName: Registration)
| Type1 | Type2 | Type3 | Type4 | Registration |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 0 | 1 | 0 | 0 | 2023-05-29 |
| 1 | 0 | 0 | 0 | 2023-04-29 |
| 0 | 0 | 1 | 0 | 2023-05-29 |
| 0 | 0 | 0 | 1 | 2023-06-29 |

I'm looking for a query which groups the data by Type1, Type2, Type3, and Type4 and shows the sums of Amount having each one month so that it returns:

Month	Type1	Type2	Type3	Type4	Total
April	1	0	0	0	1
May	0	1	1	0	2
June	0	0	0	1	1
July	0	0	0	0	0

Here is my current SQL

SELECT 
            DATEPART(month, x.RegistrationDateTime) AS Closing_Month
          , COUNT (x.Type1) ,
           COUNT (x.Type2),
           COUNT (x.Type3),
           COUNT (x.Type4)
          
     FROM(
             SELECT 
                    
                   'Type1' = 
                  CASE                  
                    WHEN r.inp = 1
                    AND bp.BusinessPartnerID = 508
                    THEN 1 ELSE 0 
                  END
                  , 'Type2' =
                  CASE                  
                    WHEN r.inp = 0
                    AND bp.BusinessPartnerID = 508
                    THEN 1 ELSE 0 
                  END
                  ,'Type3' =
                  CASE                  
                    WHEN r.inp = 1
                    AND bp.BusinessPartnerID = 509
                    THEN 1 ELSE 0 
                  END
                  , 'Type4' =
                  CASE                  
                    WHEN r.inp = 0
                    AND bp.BusinessPartnerID = 509
                    THEN 1 ELSE 0 
                  END, Registration
                 
             FROM Registration AS r 
             INNER JOIN BusinessPartner bp ON r.id = bp.RegistrationID
                  
           
              
          ) AS x
          GROUP BY DATEPART(month, x.RegistrationDateTime),Type1,Type2,Type3,Type4;

sql-server

来源：https://stackoverflow.com/questions/77226448/group-by-vertical-and-horizontal-data

1条答案

按热度按时间

yrefmtwq1#

I've created an example with this code:

DROP TABLE IF EXISTS Registration
CREATE TABLE Registration (
    Type1 INT,
    Type2 INT,
    Type3 INT,
    Type4 INT,
    RegistrationDateTime DATE
);

INSERT INTO Registration (Type1, Type2, Type3, Type4, RegistrationDateTime)
VALUES 
    (0, 1, 0, 0, '2023-01-29'),
    (1, 0, 0, 0, '2023-01-30'),
    (0, 0, 1, 0, '2023-02-15'),
    (1, 0, 0, 0, '2023-02-25'),
    (0, 0, 0, 1, '2023-03-10'),
    (1, 0, 0, 0, '2023-03-12'),
    (0, 0, 0, 1, '2023-04-01'),
    (0, 0, 0, 1, '2023-04-05'),
    (0, 0, 1, 0, '2023-04-10'),
    (0, 1, 0, 0, '2023-05-02'),
    (1, 0, 0, 0, '2023-05-15'),
    (0, 0, 1, 0, '2023-06-01'),
    (1, 0, 0, 0, '2023-06-02'),
    (0, 0, 1, 0, '2023-07-20'),
    (1, 0, 0, 0, '2023-08-12'),
    (0, 1, 0, 0, '2023-08-18'),
    (0, 0, 1, 0, '2023-09-05'),
    (0, 0, 0, 1, '2023-09-30');

Trying to replicate what you've done. And with this query:

SELECT 
    FORMAT(x.RegistrationDateTime, 'MMMM') AS Month,
    SUM(Type1) AS Type1,
    SUM(Type2) AS Type2,
    SUM(Type3) AS Type3,
    SUM(Type4) AS Type4,
    COUNT(*) AS Total
FROM (
    SELECT 
        CASE 
            WHEN r.Type1 = 1 THEN 1 ELSE 0 
        END AS Type1,
        CASE 
            WHEN r.Type2 = 1 THEN 1 ELSE 0 
        END AS Type2,
        CASE 
            WHEN r.Type3 = 1 THEN 1 ELSE 0 
        END AS Type3,
        CASE 
            WHEN r.Type4 = 1 THEN 1 ELSE 0 
        END AS Type4,
        r.RegistrationDateTime
    FROM Registration AS r 
) AS x
GROUP BY FORMAT(x.RegistrationDateTime, 'MMMM')
ORDER BY MIN(x.RegistrationDateTime);

I've created your desired output as you can see here:

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Group by Vertical and Horizontal Data

1条答案

相关问题

热门标签

最新问答