# how to calculate no.of .months billed customer

Posted by Raghuldrag under Sql Server on 12/27/2013 | Points: 10 | Views : 1666 | Status : [Member] | Replies : 3
Dear Friends,

my table looks like

Actual Table looks

Custmoer NO product name billed date
50 Biscuit 1/4/2013
50 Milk 10/4/2013
50 Milk 11/4/2013
50 Curd 15/4/2013
50 Biscuit 14/4/2013
50 Milk 16/4/2013
50 Curd 21/4/2013
50 Biscuit 19/4/2013
50 Curd 26/4/2013
50 Milk 28/4/2013
50 Biscuit 30/4/2013
40 Biscuit 1/4/2013
40 Milk 10/4/2013
40 Milk 11/4/2013
40 Curd 15/4/2013
40 Biscuit 14/4/2013
40 Milk 6/4/2013
40 Curd 21/4/2013

Expecting Output

Custmoer NO product name billed date
50 Biscuit 5
50 Milk 4
50 curd 3
40 Biscuit 2
40 Milk 3
40 curd 2

the actual table is refers the product billed for month of customer .....
now my expecting output is

How many products i billed for each customer wise ?

#### Responses

Posted by: Allemahesh on: 12/27/2013 [Member] [MVP] Silver | Points: 25
 0 Try the below one:-`SELECT CustmoerNO, product name, COUNT(billeddate) AS billeddateFROM productGROUP BY CustmoerNO, product name` Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Raghuldrag on: 12/29/2013 [Member] Starter | Points: 25
 0 Hi Friends, In these same scenario How To calculate no.of .months in that? (I.E) for the same customer milk is billed on more than 3 times on that april when i trying to taken count months mean it should be ' 1 ' .how to calculate?Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 1/3/2014 [Member] [MVP] Platinum | Points: 25
 0 May be this is what you are looking for?`SELECT distinct CustmoerNO, productName , COUNT(distinct MONTH(billedDate) ) BilledMonths , COUNT(*) BilleddateFROM TableName group by CustmoerNO, productName` Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gifRaghuldrag, if this helps please login to Mark As Answer. | Alert Moderator
Latest Posts