# Convert SQL statement to X++

**URL:** <https://www.dynamicsuser.net/t/convert-sql-statement-to-x/57764>\
**Category:** Developer Forum\
**Created:** [May 29, 2015, 1:36pm UTC](https://www.dynamicsuser.net/t/convert-sql-statement-to-x/57764 "2015-05-29T13:36:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![hariharans87](https://avatars.discourse-cdn.com/v4/letter/h/e79b87/32.png) [@hariharans87](https://www.dynamicsuser.net/u/hariharans87)\
**Post date:** [May 29, 2015, 1:36pm UTC](https://www.dynamicsuser.net/t/convert-sql-statement-to-x/57764/1 "2015-05-29T13:36:28Z")

</div>

Hi,

Please help me to convert the below SQL statement to X++. Please also consider the performance side. The main think is that count distinct function.

SELECT COMMENT\_, COUNT(DISTINCT TRANSACTIONID) FROM RetailTransactionSalesTrans

WHERE TRANSDATE BETWEEN ‘1/1/2014’ AND ‘1/2/2014’

AND DATAAREAID = ‘TES’

GROUP BY COMMENT\_

Thanks,

Hari

---

<div class="post-metadata">

**Author:** ![MartinDrab](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@MartinDrab](https://www.dynamicsuser.net/u/MartinDrab)\
**Post date:** [May 29, 2015, 1:51pm UTC](https://www.dynamicsuser.net/t/convert-sql-statement-to-x/57764/2 "2015-05-29T13:51:43Z")

</div>

Please don’t forget to add the tag with your AX version.

Do you struggle only with _COUNT(DISTINCT TRANSACTIONID)_, or do you have any additional problems?

---

<div class="post-metadata">

**Author:** ![hariharans87](https://avatars.discourse-cdn.com/v4/letter/h/e79b87/32.png) [@hariharans87](https://www.dynamicsuser.net/u/hariharans87)\
**Post date:** [May 29, 2015, 2:15pm UTC](https://www.dynamicsuser.net/t/convert-sql-statement-to-x/57764/3 "2015-05-29T14:15:03Z")

</div>

Hi Martin,

Sorry, It is AX 2012.

Yes. I struggle only with count(distinct transactionid) based on the column field.

Thanks,

Hari

---

<div class="post-metadata">

**Author:** ![MartinDrab](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@MartinDrab](https://www.dynamicsuser.net/u/MartinDrab)\
**Post date:** [May 29, 2015, 2:31pm UTC](https://www.dynamicsuser.net/t/convert-sql-statement-to-x/57764/4 "2015-05-29T14:31:06Z")

</div>

X++ doesn’t support _distinct_, nevertheless you can create a view and use _distinct_ in a computed column. I believe it’s the best workaround available.