CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
peter
peter

Reputation: 2526

Grouping and Total Issue

I have a table called Phone_Details and the data looks like:

Name            DeviceType  InvoiceDate TotalCharges
Aguilera, Alex  Smart Phone 8/3/2012    606.55
Aguilera, Alex  Data Card   8/3/2012    26.17

I want Output as:

Name            Total Spend    # of devices    Avg Spend    # of Bills >300
Aguilera, Alex  632.72              2            316.36            1

I tried doing this:

Select Name,Sum(Totalcharges), Count(DeviceType),Sum(Totalcharges)/Count(DeviceType)
from dbo.Phone_Details
group by Name

But How can i get the last column this?

Upvotes: 0

Views: 55

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

  Select Name,
         Sum(Totalcharges) [Total Spend],
         Count(DeviceType) [# of devices],
         Sum(Totalcharges)/Count(DeviceType) [Avg Spend],
         Count(CASE WHEN TotalCharges > 300 then 1 end)  [# of Bills > 300]
    from dbo.Phone_Details
group by Name

Upvotes: 2

Related Questions