kentor kentor - 3 months ago 11
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
GROUP BY
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

Answer

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`;