Zero count with product purchases

  • I am working on a SQL query that will be used in a crystal report. The problem I am tasked with is writing a report to summarize sales of certain products by the purchaser’s job code. I also need to be able to input certain job codes in the report and ensure that it show’s 0 if a sum of the purchases of a particular item for that job code are 0.

    The db I’m working with has a table for all accounts, which also stores the job code of the individual, a table for the order, a table for the line items of the order, and a separate table for the job codes and descriptions.

    The table for the job codes and descriptions has no linkable columns to the other tables needed for the query. The query below has all of the information that I will need as far as relevant orders goes. Essentially what I am looking to do is merge the query below with and get a count of each purchase of an item by job code. If no one of that job code purchased that particular item, I still need it to display zero. I’ve read that a left outer join would normally do this but with the tables having no linkable columns I’m not sure how to do that.

    Let me know if there is anything else I can provide and thanks in advance for the help.

    Query shows relevant purchases of an item and bottom query shows the query necessary for job codes and descriptions.

    SELECT

    am.name,

    am.jobcode,

    jobcode.jobdesc,

    order.orderdate,

    orderdtl.itemcode,

    FROM

    order INNER JOIN am ON

    order.acctcode = am.acctcode

    INNER JOIN jobcode ON

    jobcode.jobcode = am.jobcode

    INNER JOIN orderdtl ON

    order.ordernbr = orderdtl.ordernbr

    WHERE

    order.ordertype = 'SO' AND

    order.orderstatus = 'C' AND

    orderdtl.dept IN ('CDROM', 'VIDEO', 'CDR', 'EBOOKS','AUDCDS') AND

    order.orderdate BETWEEN '2009-08-01' and '2010-07-31' AND

    orderdtl.itemcode = 'asdf123' AND

    oderdtl.completestatus = 'Y'

    SELECT jobcode.jobcode,

    jobcode.DESC

    FROMjobcode

  • not really sure what you are trying to get but here is a guess....

    SELECT

    am.name,

    am.jobcode,

    jobcode.jobdesc,

    order.orderdate,

    orderdtl.itemcode,

    count(order.orderdate) as NumSold

    from

    .

    .

    where

    .

    .

    group by am.name,

    am.jobcode,

    jobcode.jobdesc,

    order.orderdate,

    orderdtl.itemcode

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I'm essentially looking to get a query that shows all of the job codes regardless of any purchases. I'll give it a shot but I don't think that one will quite get me what I'm looking for.

    In essence there are 122 job codes in our database. I need all 122 listed on a query no matter what. Then in a purchase count column I need to know how many of that job code purchased an item etc.

    As far as having to have it actually retrieve the info listed above all I really need it to display is job code, job description(preferably not necessary) and purchase count(for each job code). I can get the other info via a different query in the report. However in order to get accurate purchase records those tables need to be linked.

    Does that clarify at all?

  • try this

    select j.jobcode, j.jobdesc, count(order.orderdate) as NumSold

    from jobcode j

    left join am on am.jobcode = j.jobcode

    left join order on order.acctcode = am.acctcode

    where [whatever stuff here]

    group by j.jobcode, j.jobdesc

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This is close, however I had to join in the order detail table as well, since orders can have multiple items. Also it only shows job codes that actually purchased something, it doesn't show them if no one with that job code purchased anything.

    I believe the group by all function would work, however I can't do that in crystal there for i don't think that's an option for me.

    Thanks again for your help.

    select j.jobcode, j.jobdesc, count(order.orderdate) as NumSold

    from jobcode j

    left join am on am.jobcode = j.jobcode

    left join order on order.acctcode = am.acctcode

    left join orderdtl on order.ordernbr = orderdtl=ordernbr

    where [whatever stuff here]

    group by j.jobcode, j.jobdesc

  • Oh yeah :hehe:.

    You need to add an isnull to the order like (although you should count a field from order detail). That should just about get you there.

    count(isnull(order.orderdate, 0)) as NumSold

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Well we're getting there, unfortunately isnull only replaces it if it returns NULL, which this just doesn't return a row. I do however think I can use a view for this report in which case I should be able to use the group by all function. Ill let you know what I came up with.

    Thanks again.

  • bolson32 (8/10/2010)


    Well we're getting there, unfortunately isnull only replaces it if it returns NULL, which this just doesn't return a row. I do however think I can use a view for this report in which case I should be able to use the group by all function. Ill let you know what I came up with.

    Thanks again.

    If the field is from a table that is joined with a left join and there are no records present for it, ISNULL(theField,0) will return 0.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

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