CODEWITHSUNDEEP
sqlms-access
Sean Kelly
Sean Kelly

Reputation: 183

(Easy) SQL Not Returning Desired Column. Half-Working Query (Access)

I am trying to determine the change in city population grouped by state then city between two tables which look like this:

**TABLE 1**                          **TABLE 2**
NY    New York    10,000             NY    New York    -3000
NY    Syracuse     5,000             NY    Syracuse     5000
PA    Phila       12,000             PA    Phila        1000
PA    Erie        11,000             PA    Erie         4000

The query I'm using is the following:

SELECT Table1.State, Table1.City, SUMtable1.populationsum-Nz(SUMtable2.populationsum,0) AS Total
FROM (SELECT SUM(table1.populationchange) AS popsum, table1.State
      FROM Table1
      GROUP BY table1.State, table1.City) AS SUMtable1 
LEFT JOIN (SELECT SUM(table2.populationchange) AS rsum, table2.State
           FROM table2
           GROUP BY table2.State, table2.City)  AS SUMtable2 
ON SUMtable1.State= SUMtable2.State;

However, this only gives me this:

**RESULT**                                  
NY                 7000
NY                10000
PA                13000
PA                15000

I need it to also return the city. At this time, the query prompts me for a parameter value for the city. Leaving it blank and pressing enter gets me that result. How can I complete this query?

Upvotes: 0

Views: 37

Answers (2)

Mehrad Eslami
Mehrad Eslami

Reputation: 306

City is not part of FROM queries. 

SELECT State,City, sum(possum) AS Total
FROM ((
      SELECT SUM(table1.populationchange) AS popsum, table1.State as state, 
Table1.City as city
  FROM Table1
  GROUP BY table1.State, table1.City)  
Union All (
         SELECT SUM(table2.populationchange) AS rsum, table2.State as state , 
Table2.City as city
       FROM table2
       GROUP BY table2.State, table2.City) ) 
Group by state,city

Upvotes: 1

Rohit Padma
Rohit Padma

Reputation: 603

Same scenario with sample tables:

        create table #temp1(c varchar(100),s varchar(100), p int);
    create table #temp2(c varchar(100),s varchar(100), p int);
    Insert into #temp1(c,s,p) values('ny','syc',10)
    Insert into #temp1(c,s,p) values('ny','ny',10)
    Insert into #temp1(c,s,p) values('Nc','ra',10)
    Insert into #temp1(c,s,p) values('Ns','char',10)
    Insert into #temp2(c,s,p) values('ny','syc',10)
    Insert into #temp2(c,s,p) values('ny','ny',10)
    Insert into #temp2(c,s,p) values('Nc','ra',10)
    Insert into #temp2(c,s,p) values('Ns','char',10)

    select result.s,result.c,sum(p) as total  from (
    select * from #temp1 union all select * from #temp2) as result group by result.c,result.s

Exact Query : 

    select res.state,res.city,sum(res.popultationchange) as totalchange 
     from(
    select * from Table1 union all select * from Table2
    ) as res group by res.state,res.city

Hope this is what you need!

Upvotes: 0

Related Questions