CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Yogasuganya Sukumar
Yogasuganya Sukumar

Reputation: 35

count of data as ouput

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

Upvotes: 0

Views: 77

Answers (1)

Drew
Drew

Reputation: 24970

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');

Upvotes: 1

Related Questions