SQL Server Performance

sql query with join n group by

Discussion in 'T-SQL Performance Tuning for Developers' started by ashoka, Apr 24, 2007.

  1. ashoka New Member

    i have 3 tables called account,customer,packagedetail

    relationship b/w thm r like

    packagedetail.customerid = customer.customerid
    customer.accountid = account.accountid


    account table have one more colum called "month"

    if i wann see all the packages with number of customers(count) i can see it with group by query like

    SELECT packageid, COUNT (*) Customers
    FROM packagedetail, account, customer
    WHERE packagedetail.customerid = customer.customerid
    AND account.accountid = customer.accountid
    GROUP BY packageid;

    but now suppose i want to see the output like





    Packlage name JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
    P1 12 2 34 5 0 67 4 23 08 21 18 1
    p2 0 0 54 67 89 6 21 34 8 2 0 2
    p3
    p4
    .
    .
    .
    .
    .

    n so on...



    please help me for the same....
    it will really help me....
  2. khtan New Member


    SELECT packageid,
    [Jan] = sum(case when month = 1 then 1 else 0 end),
    [Feb] = sum(case when month = 2 then 1 else 0 end),
    [Mar] = sum(case when month = 3 then 1 else 0 end),
    . . .
    FROM packagedetail
    INNER JOIN customer ON packagedetail.customerid = customer.customerid
    INNER JOIN account ON account.accountid = customer.accountid
    GROUP BY packageid



    KH
  3. ashoka New Member

    hi khtan,

    its showing some error like "missing expression" ... not getting y?? can u help me ?
  4. khtan New Member

    can you post the query that you use ?


    KH
  5. ashoka New Member

    hey sorry...i was doing something wrong so it was not working...but now its working properly.....
    Thankx a lot!!
  6. khtan New Member

    I guess you did not replace the . . . with the rest of the similar codes [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Sorry, i was lazy [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br /><hr noshade size="1"><br /><font color="blue"><font size="1"><i>KH</i></font id="size1"></font id="blue">
  7. ashoka New Member

    no i replaced all the other things but i forget to remove a comma frm the query so it was showing "missing expression".... nyways now its working..<br /><br />thankx <img src='/community/emoticons/emotion-1.gif' alt=':)' />

Share This Page