SQL query doubt

  
Doubt in SQL query.
 
 

SELECT Top 5 {Sales}.[ProductName] , Sum({Sales}.[QuantitySold]), Sum({Sales}.[PaymentAmount]) from {Sales} where {Sales}.[SalesDate] between @CurrDate7 and @CurrDate group by {Sales}.[ProductName], {Sales}.[PaymentAmount] order by Sum({Sales}.[PaymentAmount]) desc

 

this is my query to show top 5 products grouped by the product name but its not grouping

 

Dashboard screen. Top products table record


First question: why don't you use an aggregate?

I am begining to notice the citizen developer low code thing does have a draw back as no one seems to know SQL or database design.....

Satish if you are on a real project and not using personal environment I would strongly advise you to use another sql client to view your data. And learn SQL there which has dedicated tools for SQL and SQL development etc. Then with the working SQL and working UNDERSTANDING and SQL profiled for optimization etc then  go back to Outsystems and OUTSYSTEMIFY it. You even write 'Doubt in SQL query.' that is why I advocate creating SQL outside of Outsystems so you have no doubt. You code wanting to get data back but you must be sure the data EXISTS first! Know your data first before doing the UI fun stuff!

Now if you dont know SQL or dont want to know SQL which is both options you should use as Kilian says Aggregates. You can learn SQL there and copy the generated SQL if you really want.

rant over.. (For the record I love low code but have always said one of the pillars of my low code manifesto is you will always need experienced 3GL guys on site but the majority of your crap 3GL guys will now be super productive but it has to be built on well modelled databases and effecient data access and general programming principles.)
 
Kilian Hekhuis wrote:

First question: why don't you use an aggregate?

 I know I should have used  aggregate but the problem is , In aggregate I don.t know how to filter only the top 5 records. In table records I can show only top five by decreasing line count to 5. But I want only the top 5 recorsd to be populated in aggregate itself.

Am I making sense?  
 
George Jeffcock wrote:
I am begining to notice the citizen developer low code thing does have a draw back as no one seems to know SQL or database design.....

Satish if you are on a real project and not using personal environment I would strongly advise you to use another sql client to view your data. And learn SQL there which has dedicated tools for SQL and SQL development etc. Then with the working SQL and working UNDERSTANDING and SQL profiled for optimization etc then  go back to Outsystems and OUTSYSTEMIFY it. You even write 'Doubt in SQL query.' that is why I advocate creating SQL outside of Outsystems so you have no doubt. You code wanting to get data back but you must be sure the data EXISTS first! Know your data first before doing the UI fun stuff!

Now if you dont know SQL or dont want to know SQL which is both options you should use as Kilian says Aggregates. You can learn SQL there and copy the generated SQL if you really want.

rant over.. (For the record I love low code but have always said one of the pillars of my low code manifesto is you will always need experienced 3GL guys on site but the majority of your crap 3GL guys will now be super productive but it has to be built on well modelled databases and effecient data access and general programming principles.)
 
 
 Actually I know some sql. I have worked on MySQL , SQL server and Oracle. But I feel its pretty different to implementing queries in sql client than from outsystems service studio. May be I have to learn a lot. I will concentrate on SQL first and will comeback to outsystems as you said.

Thanks
Sathish D wrote:
Kilian Hekhuis wrote:

First question: why don't you use an aggregate?

 I know I should have used  aggregate but the problem is , In aggregate I don.t know how to filter only the top 5 records. In table records I can show only top five by decreasing line count to 5. But I want only the top 5 recorsd to be populated in aggregate itself.

Am I making sense?  
 
 
Hi Sathish,

For you to get the top 5 records, you can set it in the aggregate property, Max Records is 5. please see picture for reference



  

Best Regards,
?Regie
 
Regie Agapito wrote:
Sathish D wrote:
Kilian Hekhuis wrote:

First question: why don't you use an aggregate?

 I know I should have used  aggregate but the problem is , In aggregate I don.t know how to filter only the top 5 records. In table records I can show only top five by decreasing line count to 5. But I want only the top 5 recorsd to be populated in aggregate itself.

Am I making sense?  
 
 
Hi Sathish,

For you to get the top 5 records, you can set it in the aggregate property, Max Records is 5. please see picture for reference



  

Best Regards,
?Regie
 
 Oh yeah. How come I didn't see that. Thanks.