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: -
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
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 ??
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
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`);
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')