[Solved] using group function excluding few fields - linking many tables without mixing its data with another table - performing queries on dynamic table

EverSQL Database Performance Knowledge Base

using group function excluding few fields - linking many tables without mixing its data with another table - performing queries on dynamic table

I have few tables of CRM App ,i am writing sql query for displaying summary of all data on dashboard.

lets say there are 3 tables, one is orders which have different client types like local, interstate, international. other is ecom orders, and the third is expenses ( which includes salary and assets purchase etc)

(Note: this is just example. real data and real tables are very big have lots of joins .. )

there is a field total_amount in all three tables.

i am trying to display : SUM(table1.total_amount) & its COUNT

NOW i want to select the same from table2 and table3 also.

all these will further sub categorised as sum(table.total_amount) & count(table.total_amount) where status is dispatched/delivered/pending etc that too overall , yearly and monthly ..

so is this possible in a single query ?

in view at some places i just need to show a only one figure.. and at some places i want to show table array with group by ..

So far i have tried case statement for this , but that makes my query really long, so i tried writing it without case statement like sum and count so and so and group by a client_id or something .. it gives me correct data.. but as i am using group it doesn't show me over all total in that .. which is correct approach for this, ? case statement or sub queries ? as i want to write all that in a single query .. or it wont work in single and i have to write separate query and function for each table ??

pleas guide .. any suggestion and examples would be appreciated .

Update: -

"DESCRIBE" of one table

CREATE TABLE `orders` (
 `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `order_num` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `client_id` int(11) NOT NULL,
 `po_num` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `sub_client_name` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
 `payment_terms` tinyint(3) unsigned NOT NULL,
 `payment_date` date DEFAULT NULL,
 `delivery_date` date DEFAULT NULL,
 `order_amount` decimal(10,0) NOT NULL,
 `tax` decimal(10,2) NOT NULL,
 `total_amount` decimal(10,2) NOT NULL,
 `order_note` text COLLATE utf8_unicode_ci,
 `marketing_by` int(10) NOT NULL,
 `dispatched_by` int(10) unsigned DEFAULT NULL,
 `dispatched_on` date DEFAULT NULL,
 `dfrom_store` int(11) NOT NULL,
 `delivered_by` int(10) unsigned DEFAULT NULL,
 `delivered_on` date DEFAULT NULL,
 `grn_id` varchar(125) COLLATE utf8_unicode_ci NOT NULL,
 `order_department` int(10) NOT NULL,
 `order_status` tinyint(3) unsigned DEFAULT '1',
 `payment_status` enum('NR','R') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'NR',
 `transporter_id` int(11) NOT NULL,
 `transporter_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `transporter_contact` bigint(20) NOT NULL,
 `transporter_cost` int(10) NOT NULL,
 `vehicle_num` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `invoice_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `excise_tax` int(10) NOT NULL,
 `notes` text COLLATE utf8_unicode_ci NOT NULL,
 `tax_flag` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
 `alert_flag` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
 `gst_tax_flag` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
 `gst_inc_tax_flag` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
 `net_margin` int(10) NOT NULL,
 `net_margin_perc` int(10) NOT NULL,
 `on_hold` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
 `is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `gt_is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `created_on` datetime NOT NULL,
 `created_by` int(11) NOT NULL,
 `modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified_by` int(11) NOT NULL,
 PRIMARY KEY (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7733 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

UPDATE 2

overall_total_amount -- overall_total_count -- over_total_amount_this_year - overall_total_count_this_year 

yearly_total_orders_amount and count .. that ie. month wise(jan, feb, mar and all) monthly_total_orders_amount and count.. (which is date wise per day of the present month)

Total_orders_amount and count client_wise - that is >

client_type1 > total_amount_over_all > total_count 

client_type2 > total_amount_over_all > total_count

and so on .. and same for yearly and monthly like

client_type1 > jan_total_amount > total_count
client_type1 > feb_total_amount > total_count
client_type2 > jan_total_amount > total_count
client_type2 > feb_total_amount > total_count

the same for monthly in which months will take place of daily dates.

///this is just for one module table but i have 12-13 like this .. i know how to do this is separate queries and php functions .. but thinking of doing this in single query.. as all tables would do the same calculations, just would return different data according to data stored in their table..the fields which i want to calculate also have the same name, so it would be so stupid of me to copy the same query 12-13 times for each table and change the table name in the end to show output .. there for i am searching a way where i can pull all data required on a dynamic table and assign a id and their respective table name in front of each and then can perform queries on it by double grouping it.. want to show all in separate divs / blocks on single page i.e dashboard page. all tables have total_amount and created, dispatched etc date fields ..

so on dashboard i can call them whenever needed .. also there would be arrays for some and there would be single field result for some .. is that possible in sql or php to have both in single function/query ? like- for sales total there would be only one field giving result and for sales yearly montly or categorywise or clientwise there would be list of arrays .. the solution i think is write a single query to displays result something like (not sure what i am saying is possible or not) date -- client_type -- category -- total_amount -- count -- status -- module_group --(dynamic_id)

and then run one more query on this table like - count all id where client_type is 1 as overall_count_client_type_1 - sum all total_amount where module_group is orders_tables and year is current year group by month (which will output array of months)

i dont know how to create layout of desired output thats why i explained in text..

UPDATE - 3

output description

i want an output something like this .. i have date wise data and total in the same table here. and client and status are stored with that particular order which is fetched with the row . if i get this then want to perform query on this .. Like- sum(total_amount) where year is 2019 and derived_from_table is retail_orders , select all where status is dispatched and derived from table is ecom..

Update 4 i have one more query somewhat related to this

SELECT
DATE_FORMAT(o.created_on, '%d-%b-%Y') AS retail_daily_date,
            SUM(o.total_amount) as retail_daily_total ,
            COUNT(o.total_amount) as retail_daily_count
        FROM
            orders o
            LEFT JOIN client c ON c.client_id = o.client_id
           WHERE
            YEAR(o.created_on) = YEAR(CURRENT_DATE())
            AND MONTH(o.created_on) = MONTH(CURRENT_DATE())
            AND c.client_type = 3
            AND o.is_deleted = 0
                        GROUP BY
          DATE_FORMAT(o.created_on, '%d-%m-%Y') 
        ORDER BY
          DATE_FORMAT(o.created_on, '%d-%Y-%m')

it will output daily order totals datewise date total count 1st jan 5000 10 2nd jan 2000 6

and so on ..

i want same table for all clients.. so each time i write same query and just change the client_type 1, 2, 3, 4, and so on to get result..

how can i do that in a single query ??

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Index Function Calls Using Generated Columns (modified query below): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index to optimize the search. Creating and indexing a generated column (supported in MySQL 5.7) will allow MySQL to optimize the search.
  3. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `client`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
Optimal indexes for this query:
ALTER TABLE `client` ADD INDEX `client_idx_client_type_client_id` (`client_type`,`client_id`);
ALTER TABLE `orders` ADD INDEX `orders_idx_year_on_month_is_dele_date_y` (`year_created_on`,`month_created_on`,`is_deleted`,`date_format_created_on_d_m_y`);
The optimized query:
SELECT
        DATE_FORMAT(o.created_on,
        '%d-%b-%Y') AS retail_daily_date,
        SUM(o.total_amount) AS retail_daily_total,
        COUNT(o.total_amount) AS retail_daily_count 
    FROM
        orders o 
    INNER JOIN
        client c 
            ON c.client_id = o.client_id 
    WHERE
        o.year_created_on = YEAR(CURRENT_DATE()) 
        AND o.month_created_on = MONTH(CURRENT_DATE()) 
        AND c.client_type = 3 
        AND o.is_deleted = 0 
    GROUP BY
        o.date_format_created_on_d_m_y 
    ORDER BY
        DATE_FORMAT(o.created_on,
        '%d-%Y-%m')

Related Articles



* original question posted on StackOverflow here.