Post reply

what can be the query??

  • February 28, 2005 at 8:21 am

    #163741

    Hi

    I have a situation like this

    Customer table,purchase table

    create table customer(

    customer_email varchar(100) not null

    ,cust_id int not null

    ,constraint pk__customer primary key clustered(cust_id)

    )

    create table purchase (

    purchase_id int not null identity(1,1)

    ,cust_id int not null references customer(cust_id)

    ,product_name varchar(200) not null

    ,amount int not null

    ,constraint pk__purchase primary key clustered (purchase_id)

    )

    I need a query that returns one row per customer with the following columns. and customers with no purchases.

    customer_email

    qty of purchase

    sum of purchase amount

  • February 28, 2005 at 8:40 am

    #544441

    Select C.cust_id, C.customer_email, COUNT(*) as PurchasesQty, SUM(P.AMOUNT) as PurchasesAmount from dbo.customer C LEFT OUTER JOIN dbo.Purchase P on C.cust_id = P.Cust_id

    if PurchasesAmount returns null then use

    ISNULL(SUM(AMOUNT)) as PurchasesAmount instead.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply