sql query to show monthly report

Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: sql query to show monthly report

  1. #1
    Join Date
    Dec 1969
    Posts
    468

    Default sql query to show monthly report

    I&#039;m going through SQL clauses which would help me to create a monthy sales report for different products sold, but I&#039;m getting confused with "Group by" statement, nested select statements. Then we can&#039;t have "Where" condition with group by. So can&#039;t figure out how to put this with "having" clause.. etc..<BR><BR>Access database, there are two tables. <BR>Orders stores invoicenum & orderdate & other order info (which we may not need)<BR>orderdetails table stores invoicenum, productid, price. (someother info)<BR><BR>how can I write SQL statment so that it takes orders only for a specific month(can be current month or last month, 3 months back)<BR>and form orderdetails table match invoicenum, show total of the same productnumber.<BR><BR>Jan <BR>produtid25 $5000.00<BR>produtid26 $500.00<BR>so on & so forth.<BR><BR>It would be a great help if you gurus can guide me,as it always has been case.!!!I&#039;ve found answers to my questions every time I post a question on this board.<BR><BR>Thanks in advance<BR><BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    11,334

    Default RE: sql query to show monthly report

    &#062;Then we can&#039;t have "Where" condition with group by<BR><BR>What???<BR><BR>SELECT fieldname, field2name<BR>FROM tablename<BR>WHERE something = &#039;something&#039;<BR>GROUP BY field2name<BR><BR>Why can&#039;t you have a where?<BR><BR>&#062;how can I write SQL statment so that it takes orders only for a specific month(can be current month or last month, 3 months <BR><BR>I don&#039;t see in your database where you have the month value.

  3. #3
    Join Date
    Dec 1969
    Posts
    468

    Default RE: sql query to show monthly report

    i&#039;ll get monthvalue from orders table by, month(orderdate) I&#039;ve used it earlier somewhere..Let me run this query, will post result of query in a minute.

  4. #4
    Join Date
    Dec 1969
    Posts
    468

    Default RE: getting error with where clause

    Error Type:<BR>Microsoft OLE DB Provider for ODBC Drivers (0x80004005)<BR>[Microsoft][ODBC Microsoft Access Driver] Cannot have aggregate function in WHERE clause <BR><BR><BR>sql = "SELECT invoicenum, orderdate, price, styleno FROM orders, orderdetails WHERE Month(orders.orderdate) = " & month(now) & "and orders.invoicenum = orderdetails.oinvoicenum and sum1 = sum(price) GROUP BY orderdetails.styleno" <BR><BR>Yes I know my sql query is not right, but I&#039;m trying to make it work by using different clauses..having, group by etc..<BR><BR>Can someone help me with this one??


  5. #5
    Join Date
    Dec 1969
    Posts
    7,686

    Default RE: getting error with where clause

    If you need an aggregate function in the WHERE clause you need to use the HAVING clause<BR><BR>SELECT * FROM Employees<BR>GROUP BY JOBID<BR>HAVING COUNT(*) &#062; 1 <BR><BR>or for your query:<BR><BR>SELECT invoicenum, orderdate, price, styleno FROM orders<BR>INNER JOIN orderdetails ON orders.InvoiceNum = orderdetails.InvoiceNum<BR>WHERE Month(orders.orderdate) =month(now) <BR>GROUP BY orderdetails.styleno<BR>HAVING sum1 = sum(price) <BR><BR>Note that this still won&#039;t work, because you can&#039;t include fields in the SELECT list that are not inlcuded in the GROUP BY clause or an aggregate function, so you need to put invoicenum, orderdate, price, styleno in the GROUP BY clause or use an aggregate function on these fields (something like MAX(Orderdate) )<BR><BR>Note2 I used the Ansi JOIN type because of readability. I like to keep the WHERE statement for filter purposes, not for the join operation but that&#039;s up to you (altough the ANSI join is faster (at least with SQL Server))<BR>

  6. #6
    Join Date
    Dec 1969
    Posts
    5,104

    Default RE: getting error with where clause

    This still probably won&#039;t work.<BR><BR>sql = "SELECT invoicenum, orderdate, price, styleno "<BR>sql = sql & "FROM orders, orderdetails "<BR>sql = sql & "WHERE Month(orders.orderdate) = " & month(now) & "and orders.invoicenum = orderdetails.oinvoicenum "<BR>sql = sql & "HAVING sum1 = sum(price) "<BR>sql = sql & "GROUP BY orderdetails.styleno" <BR><BR>What is the report and corresponding data that you are trying to retrieve?<BR><BR>You need to take a step back and think about the query.<BR><BR>-Doug

  7. #7
    Join Date
    Dec 1969
    Posts
    468

    Default RE: i'm tyring to print mth sales report

    I&#039;m trying to print monthly sales report. <BR><BR>In a given month total sale for products. Its not of all products but, by products that is<BR><BR>product1 $ 500<BR>product4 $ 1000<BR>so on & so forth<BR><BR>Orderdate is stored in orders table<BR>while productid & price comes off orderdetails table.<BR><BR>How can this be acheieved?<BR><BR>queries above doesn&#039;t work as you said..<BR><BR>Thanks

  8. #8
    Join Date
    Dec 1969
    Posts
    7,686

    Default RE: i'm tyring to print mth sales report

    Ok first: You can&#039;t have invoicenum in your select list because you want to group by product. (You probably have 1 product on multiple invoices)<BR><BR>This should work:<BR><BR>SELECT D.ProductID, SUM(D.Price) AS TotalSales FROM orders O <BR>INNER JOIN orderdetails D ON O.InvoiceNum = D.InvoiceNum <BR>WHERE MONTH(O.orderdate) = MONTH(now) <BR>GROUP BY D.ProductID<BR><BR>I&#039;m not sure why you should have an aggregate in the WHERE clause as you earlier described. If you just want a list with all products and the total sales of the current month the above query will work just fine. If you need e.g. only products with total sales &#062; $500 you do need the HAVING CLAUSE, add the following line to the query:<BR><BR>HAVING SUM(Price) &#062; 500

  9. #9
    Join Date
    Dec 1969
    Posts
    5,104

    Default So, you are only interested ...

    .. in dollar totals. You aren&#039;t actually interested in what order was placed for what items.<BR><BR>So, something like this would work:<BR>sql = "SELECT SUM(price) AS AmountOrdered, styleno "<BR>sql = sql & "FROM orders, orderdetails "<BR>sql = sql & "WHERE Month(orders.orderdate) = " & month(now) & " "<BR>sql = sql & " and orders.invoicenum = orderdetails.oinvoicenum "<BR>sql = sql & "GROUP BY orderdetails.styleno" <BR><BR>Then, you could pass the styleno to a page that details the orders that were placed for a particular item (but that&#039;s for another posting!).<BR><BR>-Doug

  10. #10
    Join Date
    Dec 1969
    Posts
    7,686

    Default beaten you twice! <eop>

    .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •