skip to Main Content

please help me to make a query.I need the total of 3 counted columns in one query as a result, here is my option:

select region, count(s_code), count(b_code), count(d_code), sum(count(s_code, b_code, d_code)) as total
from shilpy
group by region 
order by region 

3

Answers


  1. In this query, we first select the region column and then use the COUNT() function to count the occurrences of s_code, b_code, and d_code. We give each counted column an alias (s_code_count, b_code_count, d_code_count) to make it easier to reference in the result. Finally, we calculate the total count by summing up the individual counts using the + operator and give it the alias total_count. The GROUP BY clause groups the results by the region column, and the ORDER BY clause sorts the result by region

    SELECT
      region,
      COUNT(s_code) AS s_code_count,
      COUNT(b_code) AS b_code_count,
      COUNT(d_code) AS d_code_count,
      COUNT(s_code) + COUNT(b_code) + COUNT(d_code) AS total_count
    FROM
      shilpy
    GROUP BY
      region
    ORDER BY
      region;
    
    Login or Signup to reply.
  2. You can use a subquery to calculate specific counts, and then get the total of counts without having to rescan the entire table:

    select *, (count_s + count_b + count_d) as total
    from (
      select region, count(s_code) as count_s, count(b_code) as count_b, count(d_code) as count_d
      from shilpy
      group by region 
      order by region
    ) as s;
    

    Demo here

    Login or Signup to reply.
  3. If you only need the total, you can just sum it up inline:

    SELECT region, 
           COUNT(s_code) + COUNT(b_code) + COUNT(d_code) AS total
    FROM shilpy
    GROUP BY region 
    ORDER BY region
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search