January 8, 2014 at 4:14 am
Hi All,
Need your advise to improve my SQL, please. The following statement works well indeed, but
I am not sure if it will work in a same fast way, when the data will grow up.
There is a list of products (in my example below - myMainTable) and their sales history (myDataTable).
I need to build a report to show per each product (obj_id) number of sales ( a simple COUNT will do), total income
from the sales and number of units sold (quantity). The following works OK, but I use DISTINCT and don't feel comfortable
with future performance.
Thank you in advance!
WITH myMainTable AS
(SELECT 1 AS obj_id, 'A' AS obj_desc
UNION ALL
SELECT 2,'B'
UNION ALL
SELECT 3,'C'
UNION ALL
SELECT 4,'D'
UNION ALL
SELECT 5,'E'
UNION ALL
SELECT 6,'F'),
myDataTable AS (
SELECT 1 AS obj_id,
3 AS qnty,
45 AS total_price
UNION ALL
SELECT 1,4,87
UNION ALL
SELECT 1,1,10
UNION ALL
SELECT 1,40,751
UNION ALL
SELECT 2,10,8
UNION ALL
SELECT 2,100,87
UNION ALL
SELECT 3,6,9)
SELECT DISTINCT
m.obj_id,
m.obj_desc,
SUM (d.total_price) OVER (PARTITION BY m.obj_id) AS total_income,
SUM (d.qnty) OVER (PARTITION BY m.obj_id) AS total_qnty,
COUNT(d.obj_id) OVER (PARTITION BY m.obj_id) AS total_sales
FROM myMainTable m
JOIN myDataTable d
ON d.obj_id = m.obj_id
January 8, 2014 at 4:25 am
You don't need to use the over clause. You can just use group by instead:
WITH myMainTable AS
(SELECT 1 AS obj_id, 'A' AS obj_desc
UNION ALL
SELECT 2,'B'
UNION ALL
SELECT 3,'C'
UNION ALL
SELECT 4,'D'
UNION ALL
SELECT 5,'E'
UNION ALL
SELECT 6,'F'),
myDataTable AS (
SELECT 1 AS obj_id,
3 AS qnty,
45 AS total_price
UNION ALL
SELECT 1,4,87
UNION ALL
SELECT 1,1,10
UNION ALL
SELECT 1,40,751
UNION ALL
SELECT 2,10,8
UNION ALL
SELECT 2,100,87
UNION ALL
SELECT 3,6,9)
SELECT m.obj_id,
m.obj_desc,
SUM (d.total_price) as total_income,
SUM (d.qnty) AS total_qnty,
COUNT(d.obj_id) AS total_sales
FROM myMainTable m
JOIN myDataTable d
ON d.obj_id = m.obj_id
group by m.obj_id, m.obj_desc
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 8, 2014 at 4:29 am
Oh no!!! Of course! Thanks, Adi!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply