Pages

▼

Saturday, January 14, 2023

SQL Question

In Access:

SELECT Table2.category_ID AS category, count(*) AS incidents, sum(Table2.dead) AS dead, avg(Table2.dead) AS [avg]

FROM Table2

GROUP BY category_ID;

returns a table with the category table correctly looked up:


In Excel, I use Data->Get Data to connect to that query, and I get this:
Instead of using the lookup table for Category, I get the lookup index.  Any idea why?



at

1 comment:

  1. Wayne JohnsonJanuary 14, 2023 at 3:45 PM

    MS Access tries to be 'helpful' and auto-magically includes the name value from the related category record.

    Assuming the category table is called 'category' and has columns 'category_id' and 'category_name', you could use the following query:

    SELECT
    c.category_name,
    COUNT(T2.*) AS number_of_incidents,
    SUM(T2.dead) AS number_of_dead,
    AVG(T2.dead) AS average_number_of_dead
    FROM
    Table2 T2
    JOIN category c
    ON T2.category_id=c.category_id
    GROUP BY
    c.category_name;

    ReplyDelete