0

I'm trying to count product numbers for a period of time there are three tables.

invoice (table)
Column                   Column           
------------------------------------------- 
|invoicenumber (string) | datefinish(date)|
|A12345                 | 01/29/2015      |
|A12346                 | 01/30/2015      |
|A12347                 | 01/30/2015      |
------------------------------------------ 
invoiceDetails (table)
---------------------------------------------------------------
|invoicenumber (String) |productnumber (long)| Quantity(Short)|
|A12345                 |12345               |  2             |
|A12346                 |1244                |  1             |
|A12347                 |1244                |  3             |
---------------------------------------------------------------

products (table)
------------------------------------
|name(String) |productnumber(long)  |
|Book         | 12345               |
|Newspaper    | 1244                |
-------------------------------------

Here is the result I would like to get:

01/29/2015 2 Book 12345
01/30/2015 4 Newspaper 1244

I need the result set to include the: datefinish, name, productnumber and the count of the productnumber by datefinish order by datefinish, productnumber.

I can get a count of all productnumbers from the invoiceDetails like this.

select i.productnumber , c.cnt from invoicedetails i
       INNER JOIN (SELECT productnumber , count(productnumber ) as cnt
        FROM invoicedetails GROUP BY productnumber ) C ON i.productnumber = C.productnumber 

But if I add another table in the select i get and error and if I add a where in the JOIN i get and error. I tried to just get all but then I get a error: 'Invalid comparison or operation on strings with different collations.'

Select d.productnumber, p.name, t.datefinish from invoicedetails d, invoice t, product p where d.invoicenumber = t.invoicenumber and
d.productnumber=p.productnumber and t.datefinish >= '01/01/2015' and t.datefinish <= '01/31/2015'
order by t.datefinish, d.productnumber

Advantage Database by SAP

Thanks for any help, Kim

  • kindly again revise the question for better understood. – Md Haidar Ali Khan Feb 5 '15 at 6:26
  • 2
    Which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name Feb 5 '15 at 6:54
  • Could you please provide DDL (CREATE TABLE Blah...) and also DDL (INSERT INTO TABLE Blah VALUES(...)) so that those who wish to help may do so easily? Take the tour and also check out the "help us to help you" blog - both at the bottom of the page. Also, as mentioned, your DBMS would be good to know. – Vérace Feb 5 '15 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.