kentor kentor - 9 months ago 51
SQL Question

Count function on group by query

Assuming I got a table as this:

TransActions(Id, ArticleId, DateBought, DateSold)

DateSold may be null if this Transaction hasn't been sold yet. For every ArticleId I want to list how many Transactions have been Bought and Sold so far. The problem is it seems like
is not the way to go in my case. The query I have tried:

SELECT `ArticleId`, count(!ISNULL(DateBought)) AS `Bought`, count(!ISNULL(DateSold)) AS `Sold`
FROM `Transactions` AS `Transactions`
GROUP BY `Transactions`.`ArticleId`;

The problem with the above query:

It will always return the amount of rows GROUPED BY the ArticleId. So even though I have 22x DateSold = Null, it would return 22 as Sold. Here is a Screenshot of the original table for the above simplified problem. ArticleId is actually TradeSettingId:

enter image description here


count returns the number of values of the expression passed to it that are not null. With your query, every value is either true or false - i.e., not null, so all the rows are counted. To make a long story short, you just need to count the dates themselves, and leave the heavy lifting to count:

SELECT   `ArticleId`, 
         COUNT(`DateBought`) AS `Bought`,
         COUNT(`DateSold`) AS `Sold` 
FROM     `Transactions` AS `Transactions` 
GROUP BY `Transactions`.`ArticleId`;