Search code examples
sqlsql-serversubquery

Question about divide result from the same column in SQL Server

I am trying to write statement in SQL Server. What I am trying to do is to get the result of count records in columns end with "R" divide the count of all the records. So it is basically the statement of a column with a statement " count (invoice) where Invoice like "%R" / count( Invoice)"

Here is my code without the divide calculation. I only come up with statement without the divide calculation.

SELECT 
    Invoice,
    COUNT(ART_CURRENT__TRANSACTION.Invoice) AS Number_Revisions,
    MAX(ART_CURRENT__TRANSACTION.[Customer]) AS "Customer",
    MAX(ARM_MASTER__CUSTOMER.Name) AS "Name",
    MAX(ART_CURRENT__TRANSACTION.[Job]) AS Job
FROM 
    ART_CURRENT__TRANSACTION
LEFT OUTER JOIN 
    ARM_MASTER__CUSTOMER ON ARM_MASTER__CUSTOMER.Customer = ART_CURRENT__TRANSACTION.Customer
WHERE
    Invoice LIKE '%R'
GROUP BY
    Invoice;

What I am trying to ask is how can I add a column that calculate the number of invoice end with "R"/ NUMBER OF INVOICE.

Thank you guys!

Solution

  • What I am trying to do is to get the result of count records in Columns end with "R" divide the count of all the records.

    You seem to want this calculation:

    SELECT AVG(CASE WHEN t.Invoice LIKE '%R' THEN 1.0 ELSE 0 END)
FROM ART_CURRENT__TRANSACTION t;

    This assumes that invoice is in the transaction table. I don't think a join is necessary for what you want to do.