CODEWITHSUNDEEP
sqlms-accessgroup-byms-access-2010aggregate
User1974
User1974

Reputation: 386

Summarize tables with percent of sum in single query

I have an ACTIVE_TRANSPORTATION table:

+--------+----------+--------+
| ATN_ID |   TYPE   | LENGTH |
+--------+----------+--------+
|      1 | SIDEWALK |   20.6 |
|      2 | SIDEWALK |   30.1 |
|      3 | TRAIL    |   15.9 |
|      4 | TRAIL    |   40.4 |
|      5 | SIDEWALK |   35.2 |
|      6 | TRAIL    |   50.5 |
+--------+----------+--------+

It is related to an INSPECTION table via the ATN_ID:

+---------+--------+------------------+
| INSP_ID | ATN_ID | LENGTH_INSPECTED |
+---------+--------+------------------+
|     101 |      2 |             15.2 |
|     102 |      3 |              5.4 |
|     103 |      5 |             15.9 |
|     104 |      6 |             20.1 |
+---------+--------+------------------+

I want to summarize the information like this:

+----------+--------+-------------------+
|   TYPE   | LENGTH | PERCENT_INSPECTED |
+----------+--------+-------------------+
| SIDEWALK |   85.9 |               36% |
| TRAIL    |  106.8 |               23% |
+----------+--------+-------------------+

How can I do this within a single query?

Upvotes: 1

Views: 45

Answers (1)

Igor
Igor

Reputation: 316

Here is the updated answer using ACCESS 2010. Note that LENGTH is reserved in ACCESS, so it needs to be changed to LENGTH_

SELECT 
       TYPE,
       SUM(LENGTH) as LENGTH_,
       SUM(IIF(ISNULL(LENGTH_INSPECTED),0, LENGTH_INSPECTED))/SUM(LENGTH) as  PERCENT_INSPECTED
FROM 
ACTIVE_TRANSPORTATION A
LEFT JOIN INSPECTION B 
    ON A.ATN_ID = B.ATN_ID
GROUP BY TYPE

Here is the answer using T-SQL in SQL SERVER 2014 I had originally

SELECT SUM(LENGTH) as LENGTH,
       SUM(ISNULL(LENGTH_INSPECTED,0))/SUM(LENGTH) as PERCENT_INSPECTED,
       TYPE
FROM 
ACTIVE_TRANSPORTATION A
LEFT JOIN INSPECTION B 
    ON A.ATN_ID = B.ATN_ID
GROUP BY TYPE

Let me know if you need it to be converted to percent, rounded, etc, but I'm guessing that part is easy for you.

Upvotes: 2

Related Questions