I am counting the birthdays , sales , order in all 12 months from customers table in SQL server like these

In Customers table birth_date ,sale_date, order_date are columns of the table

           select 1 as ranking,'Birthdays' as Type,[MONTH],TOTAL
              from ( select DATENAME(month, birth_date)   AS [MONTH],count(*) TOTAL
                     from customers 
                     group by DATENAME(month, birth_date)                                            
                   )x

    union

            select 2 as ranking,'sales' as Type,[MONTH],TOTAL
              from ( select DATENAME(month, sale_date)   AS [MONTH],count(*) TOTAL
                     from customers 
                     group by DATENAME(month, sale_date)                    
                   )x
    union

            select 3 as ranking,'Orders' as Type,[MONTH],TOTAL
             from ( select DATENAME(month, order_date)   AS [MONTH],count(*) TOTAL
                    from customers 
                    group by DATENAME(month, order_date)
                  )x

And the output is like these(just dummy data)

I want to find count of these all these three types without using UNION and UNION ALL, means I want these data by single query statement (or more optimize version of these query)

CodePudding user response：

Here's one way, aggregating against the output of an UNPIVOT:

;WITH src(d, t, j) AS
(
  SELECT d = DATENAME(MONTH,d),
         t = v.i,
         v.j
  FROM dbo.Customers AS c
  UNPIVOT (d for col in ([birth_date],[sale_date],[order_date])) AS u
  CROSS APPLY (VALUES(1, 'birth_date', 'Birthdays'),
                     (2, 'sale_date',  'sales'),
                     (3, 'order_date', 'Orders')) AS v(i, h, j)
  WHERE u.col = v.h
)
SELECT ranking = t, 
       [Type]  = j,
       [MONTH] = d,
       [TOTAL] = COUNT(*)
FROM src GROUP BY t, j, d
ORDER BY ranking;

CodePudding user response：

Another approach is to create a CTE with all available ranking values ​​and use CROSS APPLY for it, as shown below.

WITH ranks(ranking) AS (
    SELECT * FROM (VALUES (1), (2), (3)) v(r)
)
SELECT 
    r.ranking,
    CASE WHEN r.ranking = 1 THEN 'Birthdays'
         WHEN r.ranking = 2 THEN 'Sales'
         WHEN r.ranking = 3 THEN 'Orders'
    END AS Type,
    DATENAME(month, CASE WHEN r.ranking = 1 THEN c.birth_date
                         WHEN r.ranking = 2 THEN c.sale_date
                         WHEN r.ranking = 3 THEN c.order_date
                    END) AS MONTH,
    COUNT(*) AS TOTAL
FROM customers  c
CROSS APPLY ranks r
GROUP BY r.ranking, 
         DATENAME(month, CASE WHEN r.ranking = 1 THEN c.birth_date
                              WHEN r.ranking = 2 THEN c.sale_date
                              WHEN r.ranking = 3 THEN c.order_date
                         END)
ORDER BY r.ranking, MONTH