SUM column values of specified rows via a MySQL query

Question

My Query is:

  SELECT cat_name AS category_name, SUM( cat ) AS catgory_count, SUM( total ) AS category_sum, (
  SUM( total ) *100 /  '10942263'
  ) AS Percentage
   FROM (

  SELECT a.category_name AS cat_name, COUNT( a.category_name ) AS cat, SUM( b.position ) AS total
  FROM erom.category a, erom_kmch.nsdl b
  WHERE a.bene_type_nsdl = b.bene_type
  AND a.bene_stype_nsdl = b.bene_stype
  AND b.date =  '2016-07-22'
  GROUP BY cat_name
  UNION ALL SELECT a.category_name AS cat_name, COUNT( a.category_name ) AS cat, SUM( b.shares ) AS total
  FROM erom.category a, erom_kmch.cdsl b
  WHERE a.type_cdsl = b.type
  AND a.bo_substat_cdsl = b.bo_substat
  AND b.date =  '2016-07-22'
  GROUP BY cat_name
  UNION ALL SELECT a.category_name AS cat_name, COUNT( a.category_name ) AS cat, SUM( b.shares ) AS total
  FROM erom.category a, erom_kmch.member_member_master b
  WHERE a.substatus_phy = b.substatus
  AND b.date =  '2016-07-22'
  AND shares >  '0'
  GROUP BY cat_name
  )c
 GROUP BY cat_name

  -----------------------

and i get the ouput as

  ----------------
       category_name          catgory_count             category_sum            Percentage
     Bank-Nationalised          1                            100            0.0009138877396750563
     Clearing Member            13                          2459                0.022472499518609634
     Individual- Director       1                           100             0.0009138877396750563
 Individual- Directors Relative 7                         139969                1.2791595303457794
   Individual- Promoters        1                         30000             0.2741663219025169
   Individual-Minor             1                             1             0.000009138877396750563
      ----------------------

but need the output as count(Individual- Director,Individual- Directors Relative,Individual- Promoters) as oneIndividual and the count 9

I need output like

     category_name        catgory_count             category_sum    
         One-Individual       9                        170069

i need the output after getting the first output,if anybody knows help me

All the categories you have mentioned is various from others, So seems group by will not work on this — Abdulla Nilam
– Abdulla Nilam, Commented Sep 20, 2016 at 13:31
just take what you have, and add an outer wrapper query. Queries are like onions. Add a layer. — Drew
– Drew, Commented Sep 20, 2016 at 13:32
Or just add a where clause before your final line and ditch the 4th column — Drew
– Drew, Commented Sep 20, 2016 at 13:41
Do you still need the other rows AND you additionally need this aggregated Non-Minor Individual row? Or you don't need another of the other rows? This matter because PHP can be used to filter and sum row data. If you need a purely SQL solution, it is not clear what that result set is. — mickmackusa
– mickmackusa ♦, Commented Mar 25 at 20:48

Drew · Accepted Answer · 2016-09-20 13:45:43Z

Adds an outer layer wrapper without much scrutiny of whether all of your initial query was necessary:

Select 'One-Individual' as category_name,
sum(category_count) as 'category_count',
sum(category_sum) as 'category_sum'
FROM
(
  SELECT cat_name AS category_name, SUM( cat ) AS category_count, SUM( total ) AS category_sum, (
  SUM( total ) *100 /  '10942263'
  ) AS Percentage
   FROM (

  SELECT a.category_name AS cat_name, COUNT( a.category_name ) AS cat, SUM( b.position ) AS total
  FROM erom.category a, erom_kmch.nsdl b
  WHERE a.bene_type_nsdl = b.bene_type
  AND a.bene_stype_nsdl = b.bene_stype
  AND b.date =  '2016-07-22'
  GROUP BY cat_name
  UNION ALL SELECT a.category_name AS cat_name, COUNT( a.category_name ) AS cat, SUM( b.shares ) AS total
  FROM erom.category a, erom_kmch.cdsl b
  WHERE a.type_cdsl = b.type
  AND a.bo_substat_cdsl = b.bo_substat
  AND b.date =  '2016-07-22'
  GROUP BY cat_name
  UNION ALL SELECT a.category_name AS cat_name, COUNT( a.category_name ) AS cat, SUM( b.shares ) AS total
  FROM erom.category a, erom_kmch.member_member_master b
  WHERE a.substatus_phy = b.substatus
  AND b.date =  '2016-07-22'
  AND shares >  '0'
  GROUP BY cat_name
  ) c
 GROUP BY cat_name
) d
WHERE category_name IN ('Individual- Director','Individual- Directors Relative','Individual- Promoters');

Collectives™ on Stack Overflow

SUM column values of specified rows via a MySQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related