Sort Records By Total of Group

[UltraSmooth]

I have a query that totals up sales at retail and cost by Group/Location. Up to this point I have been just ordering my results by group name (alphabetically) and by location. I need to now sort by results by the Group with the most sales.

So if my results before were like the following,
CHILD
Loc1
Loc2
LAD
Loc1
Loc2
MEN
Loc1
Loc2

If the total MEN group did the most sales, followed by CHILD and then LAD the new order for my records would be
MEN
Loc1
Loc2
CHILD
Loc1
Loc2
LAD
Loc1
Loc2

I still need to see the individual locations records for the Group, but the Groups need to be sorted from highest to lowest based on total sales for that Group.

I've looked into ROLLUP which can calculate a total by GROUP but I don't see how I could sort all my records by this 'extra' row being inserted into my resultset.

Any help would be appreciated! I'd prefer to do this within the query itself rather than in the report.

 

[r937]

you can do this by joining the results by group with the results by location

if you would kindly tell me the column names, i could show you the sql

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[UltraSmooth]

Hi r937, you've helped me a great deal in the past, good to see you're still around helping out!

My columns are

his_key (group/department)
key_desc
his_loctn (location)
ty_retail
ty_cost
ty_units
ly_retail
ly_cost
ly_units

My ORDER BY clause is

ORDER BY his_key, his_loctn

Some SQL would greatly help put me on the right track, not really seeing how this will work.

 

[r937]

SELECT grp.his_key
     , grp.sum_retail
     , grp.sum_cost
     , loc.his_loctn
     , loc_sum_retail
     , loc_sum_cost
  FROM ( SELECT his_key
              , SUM(ty_retail) AS sum_retail
              , SUM(ty_cost) AS sum_cost
           FROM daTable
         GROUP
             BY his_key ) AS grp
INNER
  JOIN ( SELECT his_key
              , his_loctn
              , SUM(ty_retail) AS sum_retail
              , SUM(ty_cost) AS sum_cost
           FROM daTable
         GROUP
             BY his_key
              , his_loctn ) AS loc
    ON loc.his_key = grp.his_key
ORDER 
    BY grp.sum_retail DESC
     , loc.his_loctn

this will give you results like

MEN Loc1
MEN Loc2
CHILD Loc1
CHILD Loc2
LAD Loc1
LAD Loc2

and you can then re-arrange this into your desired indented listing using your application language (php or whatever)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[UltraSmooth]

It makes so much sense now that I'm looking at your sql code...I don't know why that didn't come to me. Thank you so much for your help...now I'm off to implement

 

[UltraSmooth]

Rudy,

I've implemented your solution and it is giving me exactly the results I am looking for.

The sales data that I am using is the result of a fairly long query . Because I need to use the resultset of this query twice in your solution (once for the grp table, once for the loc table) I didn't think it would be efficient (or very readable) to execute this same query twice. What I did was create a 2 temporary (tmp1_salesdata, tmp2_salesdata) tables using the resultset from my sales data query and then use the final query you explained to me on these tables.

My question is would I be better off just nesting 2 copies of my sales data query or did I go the right route and dump the data to a temporary table and then copy it for a second copy to execute the final query. My total solution is posted below,

DROP TEMPORARY TABLE IF EXISTS tmp1_salesdata;

CREATE TEMPORARY TABLE tmp1_salesdata LIKE salesdata;
 
INSERT INTO tmp1_salesdata (clarion_date, his_key, item_desc, his_loctn, ty_retail, ly_retail, ty_cost, ly_cost, ty_units, ly_units)
SELECT * FROM (
  SELECT this_year.clarion_date,
         this_year.his_key,
         this_year.item_desc,
         this_year.his_loctn,
         ROUND(COALESCE(this_year.his_salretl, 0.0), 2) AS ty_retail,
         ROUND(COALESCE(last_year.his_salretl, 0.0), 2) AS ly_retail,
         ROUND(COALESCE(this_year.his_salcost, 0.0), 2) AS ty_cost,
         ROUND(COALESCE(last_year.his_salcost, 0.0), 2) AS ly_cost,
         ROUND(COALESCE(this_year.his_salunit, 0.0), 2) AS ty_units,
         COALESCE(last_year.his_salunit, 0) AS ly_units
    FROM    (  SELECT weekly_periods.clarion_date,
                      weekly_periods.fiscalyear,
                      weekly_periods.fiscalperiod,
                      syinvhis.his_key,
                      iteminfo.item_desc,
                      syinvhis.his_loctn,
                      SUM(syinvhis.his_salunit) AS his_salunit,
                      SUM(syinvhis.his_salretl) AS his_salretl,
                      SUM(syinvhis.his_salcost) AS his_salcost
                 FROM weekly_periods
                      INNER JOIN syinvhis
                         ON syinvhis.his_tdate = weekly_periods.clarion_date
                            AND syinvhis.his_type = 'DP'
                            AND syinvhis.his_loctn IN
                                     ('01', '02', '03', '04', '05')
                            AND syinvhis.his_period = 'W'
                            AND syinvhis.his_service IN (', 'R')
                      INNER JOIN (SELECT inventory.item_key,
                                         departments.item_desc
                                    FROM    (SELECT DISTINCT ih_dept AS item_key
                                               FROM syinvhdr
                                              WHERE ih_sbclass = 'HDG'
                                                    AND LENGTH(ih_dept) > 0)
                                            AS inventory
                                         LEFT JOIN
                                            (  SELECT trim(
                                                         substr(pm_element, 4, 3))
                                                         AS item_key,
                                                      pm_str_var AS item_desc
                                                 FROM syparamt
                                                WHERE pm_element LIKE 'DEP%'
                                             ORDER BY pm_element) AS departments
                                         ON inventory.item_key =
                                               departments.item_key) AS iteminfo
                         ON iteminfo.item_key = syinvhis.his_key
                WHERE (weekly_periods.fiscalyear > 2011
                       OR(weekly_periods.fiscalyear = 2011
                          AND weekly_periods.fiscalperiod >= 1))
                      AND(weekly_periods.fiscalyear < 2011
                          OR(weekly_periods.fiscalyear = 2011
                             AND weekly_periods.fiscalperiod <= 10))
             GROUP BY syinvhis.his_key, iteminfo.item_desc, syinvhis.his_loctn)
            AS this_year
         LEFT JOIN
            (  SELECT weekly_periods.clarion_date,
                      weekly_periods.fiscalyear,
                      weekly_periods.fiscalperiod,
                      syinvhis.his_key,
                      syinvhis.his_loctn,
                      SUM(syinvhis.his_salunit) AS his_salunit,
                      SUM(syinvhis.his_salretl) AS his_salretl,
                      SUM(syinvhis.his_salcost) AS his_salcost
                 FROM weekly_periods
                      INNER JOIN syinvhis
                         ON syinvhis.his_tdate = weekly_periods.clarion_date
                            AND syinvhis.his_type = 'DP'
                            AND syinvhis.his_loctn IN
                                     ('01', '02', '03', '04', '05')
                            AND syinvhis.his_period = 'W'
                            AND syinvhis.his_service IN (', 'R')
                      INNER JOIN (SELECT DISTINCT ih_dept AS item_key
                                    FROM syinvhdr
                                   WHERE ih_sbclass = 'HDG') AS iteminfo
                         ON iteminfo.item_key = syinvhis.his_key
                WHERE (weekly_periods.fiscalyear > 2010
                       OR(weekly_periods.fiscalyear = 2010
                          AND weekly_periods.fiscalperiod >= 1))
                      AND(weekly_periods.fiscalyear < 2010
                          OR(weekly_periods.fiscalyear = 2010
                             AND weekly_periods.fiscalperiod <= 10))
             GROUP BY syinvhis.his_key, syinvhis.his_loctn) AS last_year
         ON this_year.his_loctn = last_year.his_loctn
            AND this_year.his_key = last_year.his_key
ORDER BY this_year.his_key, this_year.his_loctn, this_year.clarion_date) AS salesdata;

DROP TEMPORARY TABLE IF EXISTS tmp2_salesdata;

CREATE TEMPORARY TABLE tmp2_salesdata SELECT * FROM tmp1_salesdata;

SELECT grp.his_key
     #, grp.grp_Sales
     , loc.item_desc
     , loc.his_loctn
     , loc.ty_retail
     , loc.ty_cost
     , loc.ty_units
     , loc.ly_retail
     , loc.ly_cost
     , loc.ly_units
  FROM ( SELECT his_key
              , SUM(ty_retail) AS grp_sales
           FROM tmp1_salesdata
         GROUP
             BY his_key ) AS grp
INNER
  JOIN ( SELECT his_key
              , his_loctn
              , item_desc
              , SUM(ty_retail) AS ty_retail
              , SUM(ty_cost) AS ty_cost
              , SUM(ly_retail) AS ly_retail
              , SUM(ly_cost) AS ly_cost
              , SUM(ty_units) AS ty_units
              , SUM(ly_units) AS ly_units
           FROM tmp2_salesdata
         GROUP
             BY his_key
              , his_loctn ) AS loc
    ON loc.his_key = grp.his_key
ORDER
    BY grp.grp_sales DESC
     , loc.his_loctn

 

[r937]

oy, what a query

the temp table is not a bad idea

you could also use a view, provided that you're not changing any of the parameters (like months)

the 2nd temp table seems unnecessary

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[UltraSmooth]

Hi Rudy,

Yes, it's quite the query but performs extremely fast so my indexes must be working well ! The sales table has millions and millions of records and the query is generated dynamically with lots of parameters...

I'm not really sure I can get rid of the 2nd temporary table. I cannot copy the results from my original query into the temporary table in the same query I would use for a select. So this would result in a separate query required for that step.

You cannot reference a temporary table more than once in a query so that is why I was forced to use the 2nd temporary table. I need to use temporary tables because this is a multi-user system so I want dedicated temporary tables for each user.

This is my reasoning, make any sense?

 

[r937]

sure does

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[UltraSmooth]

Thanks Rudy for you help, have a great day!