CODEWITHSUNDEEP
sql-server
AnandMeena
AnandMeena

Reputation: 578

Sql query to get total payments by customer type (1,2,3,4,5) in each month of given year

I have 2 tables Payments and Customers.

We have 5 types of customer 1,2,3,4,5.

I want to get total payments by customer type (1,2,3,4,5) in each month of given year.

And if any customer type not have any payment than it should be 0.

Below is my current query :-

 SELECT 
                    "Month" = month(o.PaymentDate)
                     , "Year" = year(o.PaymentDate)
                     , Amount = sum(o.Amount)
                     , c.CustomerTypeID                  
                FROM
                    Payments o
                    INNER JOIN
                      Customers c ON o.CustomerID = c.CustomerID
                WHERE
                    o.PaymentDate >= convert(DATETIME, '1/1/2013 12:00:00 AM')
                    AND o.PaymentDate < convert(DATETIME, '12/31/2013 12:00:00 AM')
                GROUP BY
                    month(o.PaymentDate)
                  , year(o.PaymentDate)
                  ,c.CustomerTypeID     
                ORDER BY
                    year(o.PaymentDate)
                  , month(o.PaymentDate)
                      ,c.CustomerTypeID

And Result is :-

Month Year Amout CustomerTypeID 1 2013 456 1 1 2013 678 2 1 2013 346 3 1 2013 3245 5

As currently it is not giving data for CustomerType 4, so I want to show 0 in amount column and month and Year will be same.

Can any body help me.

Thanks in advance.

Upvotes: 0

Views: 3481

Answers (3)

podiluska
podiluska

Reputation: 51494

SELECT 
    2013 as year,
    months.monthno,
    Amount = isnull(sum(o.Amount),0),
    c.CustomerTypeID  
FROM
    customers c 
         cross join     
    (select number monthNo from master..spt_values where type='p' and number between 1 and 12) months
         left join payments o
     ON o.CustomerID = c.CustomerID
     AND year(o.PaymentDate)=2013
     AND month(o.PaymentDate) = months.monthNo
GROUP BY
    months.monthno, c.CustomerTypeID
ORDER BY
    months.monthno, c.CustomerTypeID

Upvotes: 1

Yosi Dahari
Yosi Dahari

Reputation: 6999

When you use WHERE with aggregation you should seriously consider using GROUP BY ALL and not GROUP BY:

From - MS Technet:

If you use ALL, the query results include all groups produced by the GROUP BY clause, even if some of the groups have no rows that meet the search conditions. Without ALL, a SELECT statement that includes GROUP BY does not show groups for which no rows qualify.

SELECT 
                    "Month" = month(o.PaymentDate)
                     , "Year" = year(o.PaymentDate)
                     , Amount = sum(o.Amount)
                     , c.CustomerTypeID                  
                FROM
                    Payments o
                    INNER JOIN
                      Customers c ON o.CustomerID = c.CustomerID
                WHERE
                    o.PaymentDate >= convert(DATETIME, '1/1/2013 12:00:00 AM')
                    AND o.PaymentDate < convert(DATETIME, '12/31/2013 12:00:00 AM')
                GROUP BY ALL
                    month(o.PaymentDate)
                  , year(o.PaymentDate)
                  ,c.CustomerTypeID     
                ORDER BY
                    year(o.PaymentDate)
                  , month(o.PaymentDate)
                      ,c.CustomerTypeID

Upvotes: 0

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

Use Right outer join to have all customers and Coalesce(amount,0) for returning 0 instead of null.

SELECT 
                      c.CustomerTypeID                  
                FROM
                    (SELECT "Month" = month(o.PaymentDate)
                             , "Year" = year(o.PaymentDate)
                             , Amount = sum(o.Amount) 
                             , o.CustomerID
                    FROM Payments o
                    WHERE
                    o.PaymentDate >= convert(DATETIME, '1/1/2013 12:00:00 AM')
                    AND o.PaymentDate < convert(DATETIME, '12/31/2013 12:00:00 AM')
                    ) D
                    RIGHT OUTER JOIN
                      Customers c ON o.CustomerID = c.CustomerID
                GROUP BY
                    D.MONTH
                  , D.YEAR
                  ,c.CustomerTypeID     
                 ORDER BY
                       D.MONTH
                      , D.YEAR
                      ,c.CustomerTypeID

Upvotes: 0

Related Questions