Post reply

Need help with the query

  • July 14, 2006 at 2:59 am

    #113691

    Hi!

    I have got a problem writing a simple query.

    I have a table 

    create table Orders (OrderID int, CustID int, ProdID int, Quant int)

    I need to find out how many Customers who ordered one product, also ordered another. For example, count customers who orderd ProdID=1 and also ordered ProdID=2, and so on.

  • July 14, 2006 at 3:04 am

    #649172

    I would suggest using Group By;

    select CustID, ProdId, sum(Quant)

    from Orders

    Group By CustID, ProdId

  • July 14, 2006 at 3:29 am

    #649181

    Thanks, but it is not what I need.

    I'll clarify.

    create table Orders (OrderID int identity(1,1), CustID int, ProdID int, Quant int)

    insert into Orders (CustID, ProdID, Quant)

    values (1, 100, 10)

    insert into Orders (CustID, ProdID, Quant)

    values (2, 100, 10)

    insert into Orders (CustID, ProdID, Quant)

    values (1, 200, 10)

    insert into Orders (CustID, ProdID, Quant)

    values (4, 300, 10)

    insert into Orders (CustID, ProdID, Quant)

    values (5, 100, 10)

    insert into Orders (CustID, ProdID, Quant)

    values (2, 200, 10)

    Actually Quantity is not necessary here.

    Product 100 have ordered 2 customers, product 100 and 200 ordered 1 customer, and so on.

    As a result I have to get symmetric table

     100200300
    100310
    200120
    300001

    I tried

    select ProdID, CustID, count(CustID) from Orders

    group by ProdID, CustID with cube

    But it does give only part of result I need.

  • July 14, 2006 at 6:58 am

    #649226

    OK, most probably you'll have a table Customers somewhere. You can use that to find out who has ordered both:

    SELECT COUNT(distinct c.CustID)

    FROM Customers c

    JOIN Orders o ON o.CustID = c.CustID AND ProdID = 1

    JOIN Orders o ON o.CustID = c.CustID AND ProdID = 2

    Each of the joins will leave in resultset only those customers that have ordered the respective product. Together, only those that ordered both products will be displayed.

    Good when you just want to find out this... if you want to create a cross-report (symmetric table), there should be some better solution. Unfortunately, I'm not sure I understand what does the table in your example show, so I won't go into that.

    EDIT: You wrote "Product 100 have ordered 2 customers" ... is that just a typo or misunderstanding on my side? It seems to me that product 100 have ordered 3 customers. Or are you counting only those that have product 100 and no other products? But then it would be 1, not 2...

  • July 14, 2006 at 7:16 am

    #649234

    For just 2 ProdID the following query also works

    select count(a.CustID) from  (select CustID from Orders where ProdID=100) a

    join (select CustID from Orders where ProdID=200) b

    on a.CustID=b.CustID

    But I would like to have a query which populates the whole cross reference table.

  • July 14, 2006 at 8:02 am

    #649263

    This will get the data for all nonempty permutations of two products.

    You may then want to generate a dynamic crosstab from the data.

    select o1.ProdID, o2.ProdID, count(*)

    from

    (select distinct CustID, ProdID from Orders) o1

    join

    (select distinct CustID, ProdID from Orders) o2

    on

    o1.CustID = o2.CustID

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • July 14, 2006 at 8:45 am

    #649283

    Great, thanks.

    Query only lacks GROUP BY clause

  • July 14, 2006 at 8:47 am

    #649285

    Can you guess what it should be?

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • July 14, 2006 at 9:38 am

    #649305

    Sure

    And any ideas how to preset the results in the form of matrix? Pivot is not smart in SQL Server 2000.

    And how to get 0 in the results when there is no row count?

  • July 15, 2006 at 4:28 am

    #649400

    --to generate the crosstab:
     

    --the code is written to minimise use of dynamic SQL and maximise scalability.
    --the only absolute limitation is the number of columns (salesperson IDS).
     

    --this uses salespersons instead of products
    --use AdventureWorks
     

    drop

    table #Salesperson

    go

    set

    nocount on

    go

    create

    table #Salesperson (SalespersonID int not null unique clustered)

    insert

    #Salesperson

    --if you want all sales persons up to max possible columns (or add a where clause)

    select

    top 1023 SalesPersonID from Sales.SalesPerson order by SalesPersonID

    /*
    --if you only want those with orders
    select top 1023 distinct SalesPersonID from Sales.SalesOrderHeader
    */

    declare

    @cr_SalesPerson int, @sql varchar(8000)

    select

    @cr_SalesPerson = min(SalesPersonID) from #Salesperson

    while

    @cr_SalesPerson is not null

    begin

    select @sql = 'alter table #Salesperson add [' + ltrim(str(@cr_SalesPerson)) + '] int not null default 0'
    --print @sql
    exec(@sql)
    select @cr_SalesPerson = null, @cr_SalesPerson = min(SalesPersonID) from #Salesperson
    where SalesPersonID > @cr_SalesPerson

    end
    select

    @cr_SalesPerson = min(SalesPersonID) from #Salesperson

    while

    @cr_SalesPerson is not null

    begin

    select @sql = ' update s1
    set [' + ltrim(str(@cr_SalesPerson)) + '] = isnull(cnt,0)
    from #Salesperson s1
    left join
    (
    select soh.SalesPersonID, count(distinct CustomerID) cnt
    from Sales.SalesOrderHeader soh
    where exists(
    select soh2.SalesPersonID
    from Sales.SalesOrderHeader soh2
    where soh2.CustomerID = soh.CustomerID
    and soh2.SalesPersonID = ' + ltrim(str(@cr_SalesPerson)) + ')
    group by soh.SalesPersonID
    ) t1
    on t1.SalesPersonID = s1.SalesPersonID'
    print @sql
    exec(@sql
    select @cr_SalesPerson = (select min(SalesPersonID) from #Salesperson where SalesPersonID > @cr_SalesPerson)
    end
    select * from #Salesperson

     

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • July 18, 2006 at 5:44 am

    #649751

    I am sorry, I did not get what this script does and how it works in my case. I need to write a query which returns "0" instead of rows which do not appear in the result set.

  • July 18, 2006 at 9:54 am

    #649851

    I thought you also wanted a crosstab/pivot table...?

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 12 posts - 1 through 11 (of 11 total)

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