new to SQL, a little help please

new to SQL, a little help please

  

Hoping somone can help me with this probably very easy SQL I'm trying to write. 

Basically I want to search through a list, and find the distinct instances of a OrderStatusId and then count how many instances of this where a SupplierId=SupplierIdVariable 


My "SPO" entity has four attributes: ID, SupplierId, SPOStatus, Date

I want to return:

Distinct SPOStatus, Count,


My SQL thus far is:


SyntaxEditor Code Snippet

SELECT {SPO}.[SupplierId],
Count(Distinct {SPO}.[SPOStatus])
From
{SPO}
where {SPO}.[SupplierId] = @SupplierId 


Can someone point me in the right direction? 

Solution

Hi Talis,

Is it something like this what you want?

SELECT {SPO}.[SPOStatus], Count(*)
From {SPO}
where {SPO}.[SupplierId] = @SupplierId
GROUP BY {SPO}.[SPOStatus]

This will count the number of records for each Status that have the supplier equal to the variable.

Let me know if that is what you wanted.

Cheers,

José

Solution

Absolutely spot on, you're a prince among men Jose, thank you very much! 

Glad I could help.

Let me just say that you could had done the same using an aggregate and that will make your code easier to maintain and automatically optimised by OutSystems.

Cheers,

José

José Costa wrote:

Glad I could help.

Let me just say that you could had done the same using an aggregate and that will make your code easier to maintain and automatically optimised by OutSystems.

Cheers,

José

how can you group in aggregates (or distinct)? I've not done this before 


Hi Talis,

In the aggregate, for each attribute, you can click on the upper right to get some options for that attribute. One of them is the group by. See the screenshot below:

You have also a video lesson about aggregates and SQL queries with more details here:

https://www.outsystems.com/learn/lesson/858/data-queries/

Cheers,

José

I am a fresher and I want create a career in web and app development and I want to be an expert in SQL, so anyone can tell me how can I learn SQL!