Numbers are not grouping together

TDaily
TDaily Contributor
edited March 2023 in Datasets

I am trying to make a chart using different sale data. Currently I have an issue of certain numbers not grouping together. I have attached a picture to show what I am talking about. I have tried using the SQL tool to group the data together but I cannot get that to work. I have posted my SQL code below as well. Any ideas or help would be awesome. 

 

 

Screen Shot 2017-01-23 at 4.19.07 PM.png

 

SELECT
(sales_amt/trans_count),

sales_amt,
.

.

.

.
begindate
FROM database
WHERE type = 'SERVICE FEES'

GROUP BY (sales_amt/trans_count) ;

 

Thank you

Taylor 

Thank you
TDaily

Best Answer

  • TDaily
    TDaily Contributor
    Answer ✓

    What I did to get it to work was kind of like Aaron suggested, I used SQL to create a new output table and rounded the calculation to the closest 2 decimals and that solved the issue. 

     

    SELECT

    ROUND(sales_amt/trans_count,2) as fee,

    trans_count as Transactions,

    .

    .

    .

    .

    FROM Database;

     

    Thank you
    TDaily

Answers

  • Do you have an aggregation of some kind in your SELECT statement?  Like a SUM() or COUNT()?

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • TDaily
    TDaily Contributor

    No I don't have any aggregations of any kind in my SELECT statement. 

    Thank you
    TDaily
  • To get values to group together there would need to be an aggregation of some sort.  Either on the SQL or on the table card.  For example, you could sum the transaction column but not the amount column to get the number of transactions per transaction amount.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • TDaily
    TDaily Contributor

    Thnak you for your help and looking into my issue.

     

    On the Domo side I am summing the transactions. So I have that aggregation. It is like Domo doesn't recongize that those two $5 numbers are the same. I was thinking it could possibly be slighlty different like 10 decial places. I don't know if that is what it is but I know with some computer calutaltions stuff like that can happen. 

     

    $5.00000000001

    $5.00000000000

    Thank you
    TDaily
  • That's very possible.  To test that, create a beast mode that just looks at the first two decimals.  Try a ROUND() function.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • TDaily
    TDaily Contributor
    Answer ✓

    What I did to get it to work was kind of like Aaron suggested, I used SQL to create a new output table and rounded the calculation to the closest 2 decimals and that solved the issue. 

     

    SELECT

    ROUND(sales_amt/trans_count,2) as fee,

    trans_count as Transactions,

    .

    .

    .

    .

    FROM Database;

     

    Thank you
    TDaily