I have a table from our cash register where clients buy stuff. It happens in our environment a lot that one client buys the same article. Example:

Now I want to have a list like this (because client 5 has bought product 3 for 3 times in total):

Also I need to know what product product 3 is, so there is a join structure set up from table Articles.

And a where clause, because I only want the sales from after a date.

All of that apart works great, but when I add them all together, nothing happens. Help? :)

I got this:

$sqlgegevens = "SELECT SUM(number) AS SumPerArticleProduct, 
                       articleid, 
                       price, 
                       number, 
                       client, 
                       salesdate 
                FROM Sales 
                INNER JOIN Articles ON Sales.articleid= Articles.Articleid 
                WHERE salesdate > '$date1'  
                GROUP BY client, articleid
              ";

CodePudding user response：

You're breaking your GROUP BY-clause by selecting the number-field in both the aggregate SUM(number) and singular number form.

$sqlgegevens = "
       SELECT SUM(number) AS SumPerArticleProduct, 
              articleid, price, client, salesdate
         FROM Sales 
   INNER JOIN Articles 
           ON Sales.articleid= Articles.Articleid 
        WHERE salesdate > '$date1'
     GROUP BY client, articleid
";

Also, not directly related to your question, but you really shouldn't use variables directly inside an SQL query (and especially not if the value comes from userinput, which is even likely here): WHERE salesdate > '$date1'. I haven't seen your query statement, but it is likely your page has security vulnerabilities at this time.

See Example #2 in the PHP manual here for a much better/safer way to query a database.

CodePudding user response：

Okay, alongside with the double select from @Raxi, I needed to add the tables to the "group by" clausule. It should be Sales.clientid and Sales.articleid. Now it works!