Get percentage of total using sub selection-CodePudding

I have a query that sums sold units and groups them by store. See code and/or SQL-fiddle below:

http://www.sqlfiddle.com/#!5/95f29/6

SELECT stores,
  sum(shoes) as sold_shoes,
  sum(gloves) as sold_gloves,
  sum(trousers) as sold_trousers,
 
  sum(shoes)  
  sum(gloves)  
  sum(trousers) as sold_total

  FROM t
  GROUP BY stores

However, I also want the percentage sold by each unit to the total and have therefore created a column named "sold_total". After the output of this query is generated I need to save it and do another query to get the percentage, see below:

select *, 
round(cast(sold_shoes AS FLOAT)/cast(sold_total AS FLOAT)*100,2) as sold_shoes_perc,
round(cast(sold_gloves AS FLOAT)/cast(sold_total AS FLOAT)*100,2) as sold_gloves_perc,
round(cast(sold_trousers AS FLOAT)/cast(sold_total AS FLOAT)*100,2) as sold_trousers_perc
from t_new

There's got to be a way to "merge" these two queries into one, and from my understanding, you could use sub-queries which I have tried but given the small SQL-knowledge I haven't got it t work.

Any help out there?

CodePudding user response：

I think, I have a solution for you. You can use CTE. Here is my code given Below

CREATE TABLE t([id] int, [stores] varchar(20),  [shoes] int, [gloves] int, [trousers] int );
    
INSERT INTO t([id], [stores], [shoes], [gloves], [trousers]) VALUES
    (1, 'New York', 4, 5, 7),
    (2, 'New York', 5, 1, 1),
    (3, 'New York', 2, 2, 4),
    (4, 'New York', 0, 5, 6),
    (5, 'Boston', 4, 4, 6),
    (6, 'Boston', 9, 3, 5),
    (7, 'Boston', 1, 2, 4),
    (8, 'Washington', 2, 5, 1),
    (9, 'Washington', 5, 2, 5);

WITH CTE AS
    (
      SELECT stores,
          sum(shoes) as sold_shoes,
          sum(gloves) as sold_gloves,
          sum(trousers) as sold_trousers,
         
          sum(shoes)  
          sum(gloves)  
          sum(trousers) as sold_total
    FROM t
    GROUP BY stores
      )
      Select CTE.*,
             (cast(sold_shoes AS FLOAT)/cast(sold_total AS FLOAT) *100.0 ) AS sold_shoesPercentage,
             (cast(sold_gloves AS FLOAT)/cast(sold_total AS FLOAT) *100.0 ) AS sold_glovesPercentage,
             (cast(sold_trousers AS FLOAT)/cast(sold_total AS FLOAT) *100.0 ) AS sold_trousersPercentage
      from CTE

Note: This code is written in SQLite. Please check the link => sqlfiddle