CODEWITHSUNDEEP
sqlgroup-by
Nathan
Nathan

Reputation: 25016

SQL group by issue when i try to get some info

I can not figure it out: I have a table called ImportantaRecords with fields like market, zip5, MHI, MHV, TheTable and I want to group all the records by zip5 that have TheTable = 'mg'… I tried this :

select a.Market,a.zip5,count(a.zip5),a.MHI,a.MHV,a.TheTable from 
    (select * from ImportantaRecords where TheTable = 'mg') a
    group by a.Zip5

but it gives me the classic error with not an aggrefate function

and then I tried this:

select Market,zip5,count(zip5),MHI,MHV,TheTable from ImportantaRecords where TheTable = 'mg'
    group by Zip5

and the same thing…

any help ?

Upvotes: 0

Views: 34

Answers (1)

Taryn
Taryn

Reputation: 247880

You did not state what database you are using but if you are getting an error about columns not being in an aggregate function, then you might need to add the columns not in an aggregate function to the GROUP BY:

select Market,
  zip5,
  count(zip5),
  MHI,
  MHV,
  TheTable 
from ImportantaRecords 
where TheTable = 'mg'
group by Market, Zip5, MHI, MHV, TheTable;

If grouping by the additional columns alters the result that you are expecting, then you could use a subquery to get the result:

select i1.Market,
  i1.zip5,
  i2.Total,
  i1.MHI,
  i1.MHV,
  i1.TheTable 
from ImportantaRecords i1
inner join
(
  select zip5, count(*) Total
  from ImportantaRecords
  where TheTable = 'mg'
  group by zip5
) i2
  on i1.zip5 = i2.zip5
where i1.TheTable = 'mg'

Upvotes: 1

Related Questions