Styjun

What should come first—characters or plot?

Does maintaining a spell with a longer casting time count as casting a spell?

How to check whether a sublist exist in a huge database lists in a fast way?

Billiard balls collision

Why does Windows store Wi-Fi passwords in a reversible format?

How to maximize the drop odds of the Essences in Diablo II?

Why doesn't 'd /= d' throw a division by zero exception?

What does "rel" in `mathrel` and `stackrel` stands for?

“T” in subscript in formulas

Duplicate instruments in unison in an orchestra

Round towards zero

Was the Boeing 2707 design flawed?

How were medieval castles built in swamps or marshes without draining them?

Boot Windows from SAN

Are the players on the same team as the DM?

Macro inserted via everypar in obeylines context doesn't see some commands

What is the loud noise of a helicopter when the rotors are not yet moving?

Do Bayesian credible intervals treat the estimated parameter as a random variable?

Can you cast bonus action and reaction spells while already casting a spell?

What is the difference between "Grippe" and "Männergrippe"?

Filling a listlineplot with a texture

"There were either twelve sexes or none."

Cooking Scrambled Eggs

Movie where people enter a church but find they can't leave, not in English

Query to show new sales growth for current year

MySQL Query GROUP BY day / month / yearHow to show the last queries executed on MySQL?MYSQL joining multiple queries based on conditionReal world total sales SQL queryExport data from Relationship betwen 3 tables with functions in single queryHow to select items which sells increased compared to historical averageSQL Query taking forever is my query properly formatted?MySQL SELECT this year vs thist time last yearLoad a multidimensional array from 2 database queries and 2 loopsSQL Query to select data based on year and month

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I'm trying to find new sales for this year, defined as new customers we sold to this year and not last, or new items we sold to existing customers this year that we didn't sell last year.

select sales1.Rep, sum(sales1.InvPrice) as newsales from sales sales1
where YEAR(sales1.InvDate)=YEAR(CURDATE()) and not exists (select 1
from sales sales2
where YEAR(sales2.InvDate)=YEAR(DATE_ADD(CURDATE(), INTERVAL -1, YEAR)) and 
CONCAT(sales1.CustNo, sales1.Item)=CONCAT(sales2.CustNo, sales2.Item) limit 1)
group by sales1.Rep

The query returns sales dollars for each rep, but it takes over an hour to run. Is there a better way to write this query so that it won't take so long? My sales table has approximately 600,000 rows in it from 1/1/2017 to current.

mysql

share|improve this question

edited Mar 27 at 19:20

Alex

1471111 bronze badges

asked Mar 27 at 19:16

RandyRandy

155 bronze badges

add a comment | 

0

I'm trying to find new sales for this year, defined as new customers we sold to this year and not last, or new items we sold to existing customers this year that we didn't sell last year.

select sales1.Rep, sum(sales1.InvPrice) as newsales from sales sales1
where YEAR(sales1.InvDate)=YEAR(CURDATE()) and not exists (select 1
from sales sales2
where YEAR(sales2.InvDate)=YEAR(DATE_ADD(CURDATE(), INTERVAL -1, YEAR)) and 
CONCAT(sales1.CustNo, sales1.Item)=CONCAT(sales2.CustNo, sales2.Item) limit 1)
group by sales1.Rep

The query returns sales dollars for each rep, but it takes over an hour to run. Is there a better way to write this query so that it won't take so long? My sales table has approximately 600,000 rows in it from 1/1/2017 to current.

mysql

share|improve this question

edited Mar 27 at 19:20

Alex

1471111 bronze badges

asked Mar 27 at 19:16

RandyRandy

155 bronze badges

add a comment | 

I'm trying to find new sales for this year, defined as new customers we sold to this year and not last, or new items we sold to existing customers this year that we didn't sell last year.

select sales1.Rep, sum(sales1.InvPrice) as newsales from sales sales1
where YEAR(sales1.InvDate)=YEAR(CURDATE()) and not exists (select 1
from sales sales2
where YEAR(sales2.InvDate)=YEAR(DATE_ADD(CURDATE(), INTERVAL -1, YEAR)) and 
CONCAT(sales1.CustNo, sales1.Item)=CONCAT(sales2.CustNo, sales2.Item) limit 1)
group by sales1.Rep

The query returns sales dollars for each rep, but it takes over an hour to run. Is there a better way to write this query so that it won't take so long? My sales table has approximately 600,000 rows in it from 1/1/2017 to current.

mysql

share|improve this question

edited Mar 27 at 19:20

Alex

1471111 bronze badges

asked Mar 27 at 19:16

RandyRandy

155 bronze badges

select sales1.Rep, sum(sales1.InvPrice) as newsales from sales sales1
where YEAR(sales1.InvDate)=YEAR(CURDATE()) and not exists (select 1
from sales sales2
where YEAR(sales2.InvDate)=YEAR(DATE_ADD(CURDATE(), INTERVAL -1, YEAR)) and 
CONCAT(sales1.CustNo, sales1.Item)=CONCAT(sales2.CustNo, sales2.Item) limit 1)
group by sales1.Rep

share|improve this question

edited Mar 27 at 19:20

Alex

1471111 bronze badges

asked Mar 27 at 19:16

RandyRandy

155 bronze badges

share|improve this question

edited Mar 27 at 19:20

Alex

1471111 bronze badges

asked Mar 27 at 19:16

RandyRandy

155 bronze badges

edited Mar 27 at 19:20

Alex

1471111 bronze badges

edited Mar 27 at 19:20

Alex

1471111 bronze badges

asked Mar 27 at 19:16

RandyRandy

155 bronze badges

asked Mar 27 at 19:16

RandyRandy

155 bronze badges

1 Answer
1

active

oldest

votes

0

You can try this, but do you have the right indexes.
if not please post the EXPLAIN from the Query.

SELECT sales1.Rep
 , sum(sales1.InvPrice) as newsales
FROM sales sales1
WHERE sales1.InvDate BETWEEN DATE_FORMAT(NOW(),'%Y-01-01 00:00:00') AND DATE_FORMAT(NOW(),'%Y-12-31 23:59:59')
 AND NOT EXISTS (
 SELECT 1 FROM sales sales2
 WHERE sales2.InvDate BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 YEAR '%Y-01-01 00:00:00')
 AND DATE_FORMAT(NOW() - INTERVAL 1 YEAR,'%Y-12-31 23:59:59')
 AND ( sales1.CustNo = sales2.CustNo AND sales1.Item = sales2.Item)
 LIMIT 1)
GROUP BY sales1.Rep;

share|improve this answer

answered Mar 27 at 19:37

Bernd BuffenBernd Buffen

10.7k22 gold badges1616 silver badges2424 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you! That cut the time down from about an hour to just a few minutes.
  
  – Randy
  Mar 28 at 18:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  check the index or post the explain of the query. the time is to much
  
  – Bernd Buffen
  Mar 28 at 18:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | PRIMARY | sales1 | index | NULL | Rep | 768 | NULL | 569173 | Using where 2 | DEPENDENT | SUBQUERY | sales2 | ref | CustNo | CustNo | 768 | asi.sales1.CustNo | 265 | Using where
  
  – Randy
  Mar 29 at 19:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks, can you also send the output from show create table sales; to see what indexes are exists
  
  – Bernd Buffen
  Mar 29 at 19:32
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55384921%2fquery-to-show-new-sales-growth-for-current-year%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

You can try this, but do you have the right indexes.
if not please post the EXPLAIN from the Query.

SELECT sales1.Rep
 , sum(sales1.InvPrice) as newsales
FROM sales sales1
WHERE sales1.InvDate BETWEEN DATE_FORMAT(NOW(),'%Y-01-01 00:00:00') AND DATE_FORMAT(NOW(),'%Y-12-31 23:59:59')
 AND NOT EXISTS (
 SELECT 1 FROM sales sales2
 WHERE sales2.InvDate BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 YEAR '%Y-01-01 00:00:00')
 AND DATE_FORMAT(NOW() - INTERVAL 1 YEAR,'%Y-12-31 23:59:59')
 AND ( sales1.CustNo = sales2.CustNo AND sales1.Item = sales2.Item)
 LIMIT 1)
GROUP BY sales1.Rep;

share|improve this answer

answered Mar 27 at 19:37

Bernd BuffenBernd Buffen

10.7k22 gold badges1616 silver badges2424 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you! That cut the time down from about an hour to just a few minutes.
  
  – Randy
  Mar 28 at 18:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  check the index or post the explain of the query. the time is to much
  
  – Bernd Buffen
  Mar 28 at 18:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | PRIMARY | sales1 | index | NULL | Rep | 768 | NULL | 569173 | Using where 2 | DEPENDENT | SUBQUERY | sales2 | ref | CustNo | CustNo | 768 | asi.sales1.CustNo | 265 | Using where
  
  – Randy
  Mar 29 at 19:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks, can you also send the output from show create table sales; to see what indexes are exists
  
  – Bernd Buffen
  Mar 29 at 19:32
  

  
  
  
  

add a comment | 

0

You can try this, but do you have the right indexes.
if not please post the EXPLAIN from the Query.

SELECT sales1.Rep
 , sum(sales1.InvPrice) as newsales
FROM sales sales1
WHERE sales1.InvDate BETWEEN DATE_FORMAT(NOW(),'%Y-01-01 00:00:00') AND DATE_FORMAT(NOW(),'%Y-12-31 23:59:59')
 AND NOT EXISTS (
 SELECT 1 FROM sales sales2
 WHERE sales2.InvDate BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 YEAR '%Y-01-01 00:00:00')
 AND DATE_FORMAT(NOW() - INTERVAL 1 YEAR,'%Y-12-31 23:59:59')
 AND ( sales1.CustNo = sales2.CustNo AND sales1.Item = sales2.Item)
 LIMIT 1)
GROUP BY sales1.Rep;

share|improve this answer

answered Mar 27 at 19:37

Bernd BuffenBernd Buffen

10.7k22 gold badges1616 silver badges2424 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you! That cut the time down from about an hour to just a few minutes.
  
  – Randy
  Mar 28 at 18:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  check the index or post the explain of the query. the time is to much
  
  – Bernd Buffen
  Mar 28 at 18:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | PRIMARY | sales1 | index | NULL | Rep | 768 | NULL | 569173 | Using where 2 | DEPENDENT | SUBQUERY | sales2 | ref | CustNo | CustNo | 768 | asi.sales1.CustNo | 265 | Using where
  
  – Randy
  Mar 29 at 19:24
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thanks, can you also send the output from show create table sales; to see what indexes are exists
  
  – Bernd Buffen
  Mar 29 at 19:32
  

  
  
  
  

add a comment | 

You can try this, but do you have the right indexes.
if not please post the EXPLAIN from the Query.

SELECT sales1.Rep
 , sum(sales1.InvPrice) as newsales
FROM sales sales1
WHERE sales1.InvDate BETWEEN DATE_FORMAT(NOW(),'%Y-01-01 00:00:00') AND DATE_FORMAT(NOW(),'%Y-12-31 23:59:59')
 AND NOT EXISTS (
 SELECT 1 FROM sales sales2
 WHERE sales2.InvDate BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 YEAR '%Y-01-01 00:00:00')
 AND DATE_FORMAT(NOW() - INTERVAL 1 YEAR,'%Y-12-31 23:59:59')
 AND ( sales1.CustNo = sales2.CustNo AND sales1.Item = sales2.Item)
 LIMIT 1)
GROUP BY sales1.Rep;

share|improve this answer

answered Mar 27 at 19:37

Bernd BuffenBernd Buffen

10.7k22 gold badges1616 silver badges2424 bronze badges

SELECT sales1.Rep
 , sum(sales1.InvPrice) as newsales
FROM sales sales1
WHERE sales1.InvDate BETWEEN DATE_FORMAT(NOW(),'%Y-01-01 00:00:00') AND DATE_FORMAT(NOW(),'%Y-12-31 23:59:59')
 AND NOT EXISTS (
 SELECT 1 FROM sales sales2
 WHERE sales2.InvDate BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 YEAR '%Y-01-01 00:00:00')
 AND DATE_FORMAT(NOW() - INTERVAL 1 YEAR,'%Y-12-31 23:59:59')
 AND ( sales1.CustNo = sales2.CustNo AND sales1.Item = sales2.Item)
 LIMIT 1)
GROUP BY sales1.Rep;

share|improve this answer

answered Mar 27 at 19:37

Bernd BuffenBernd Buffen

10.7k22 gold badges1616 silver badges2424 bronze badges

answered Mar 27 at 19:37

Bernd BuffenBernd Buffen

10.7k22 gold badges1616 silver badges2424 bronze badges

answered Mar 27 at 19:37

Bernd BuffenBernd Buffen

10.7k22 gold badges1616 silver badges2424 bronze badges