CODEWITHSUNDEEP
mysqlsql
dude
dude

Reputation: 4962

How to select the sum of distinct values in a table using mysql

slno state district company 
1     a       ab     abc
2     a       ab     ac
3     a       ab     abc
4     a       ab     abc
5     a       ab     abc
6     a       ab     ac

output

state district company sumzz
 a      ab       abc    4
 a      ab       ac     2

what i have tried 

select state,district,company,sum(company) as sumzz from company

how to achieve this display the last row based on distinct company?

Upvotes: 0

Views: 355

Answers (4)

databyss
databyss

Reputation: 6488

select state,district,company,sum(company) as sumzz from company where company = 'abc'

Upvotes: 0

Lamak
Lamak

Reputation: 70638

I think that you are trying to count how many records there are instead of suming something. If that is what you want, then you should be doing:

SELECT state, district, company, COUNT(*) as sumzz 
FROM company
GROUP BY state, district, company

Upvotes: 2

raina77ow
raina77ow

Reputation: 106385

And what's wrong with this approach:

  SELECT state, district, company, COUNT(*) as countzz
    FROM company
GROUP BY state, district, company

Upvotes: 2

Andomar
Andomar

Reputation: 238086

EDIT: For the updated question, you could retrieve the maximum slno in a subquery. Then you can use in to filter for those:

select  state
,       district
,       company
,       slno
from    Company
where   slno in
        (
        select  max(slno)
        from    Company
        group by
                state
        ,       district
        ,       company
        )

Upvotes: 2

Related Questions