Mryntu

How to make readers know that my work has used a hidden constraint?

Question about partial fractions with irreducible quadratic factors

Running a subshell from the middle of the current command

Is going from continuous data to categorical always wrong?

Potentiometer like component

How do anti-virus programs start at Windows boot?

Provisioning profile doesn't include the application-identifier and keychain-access-groups entitlements

Can "semicircle" be used to refer to a part-circle that is not a exact half-circle?

How to deal with a cynical class?

Can infringement of a trademark be pursued for using a company's name in a sentence?

Confusion with the nameplate of an induction motor

Sword in the Stone story where the sword was held in place by electromagnets

Am I not good enough for you?

Life insurance that covers only simultaneous/dual deaths

How is the Swiss post e-voting system supposed to work, and how was it wrong?

Rejected in 4th interview round citing insufficient years of experience

Counter-example to the existence of left Bousfield localization of combinatorial model category

Giving Plot options defined outside of the Plot expression

If Invisibility ends because the original caster casts a non-concentration spell, does Invisibility also end on other targets of the original casting?

Why don't MCU characters ever seem to have language issues?

Playing ONE triplet (not three)

Are there situations where a child is permitted to refer to their parent by their first name?

What is the definition of "Natural Selection"?

Can the druid cantrip Thorn Whip really defeat a water weird this easily?

Magento get list of sales/revenue per brand/manufacturer

Dashboard's Lifetime Sales And Total Order Revenue are displaying different values ? Any Solutions?Magento Sales Reports not UpdatingLow Stock Report: How to Add a Manufacturer/Brand column?Filters on custom product listing page not workingSales Revenue per product from sales/orderHow to get Lifetime Sales Data in Magento 2Magento Showing incorrect sales order reportHow get the total revenue of a productShop by Brand/Manufacturer custom URL in M1How to change the field Product Brand to Manufacturer?

2

How can one get a quick list of sales/revenue per brand/manufacturer in Magento?

Something like

Brand Sales (minus returned/cancel)
brand 1 100 euro
brand 2 200 euro

Difficulty I see is: grouping by a product attribute. Correcting for returned and cancelled. And one could add a filter over time (from date - to date)

Any help appreciated

attributes sales reports

share|improve this question

edited Sep 20 '16 at 7:43

Fabian Schmengler

54.9k21134350

asked Sep 19 '16 at 10:05

snh_nlsnh_nl

2,8931046104

add a comment | 

2

How can one get a quick list of sales/revenue per brand/manufacturer in Magento?

Something like

Brand Sales (minus returned/cancel)
brand 1 100 euro
brand 2 200 euro

Difficulty I see is: grouping by a product attribute. Correcting for returned and cancelled. And one could add a filter over time (from date - to date)

Any help appreciated

attributes sales reports

share|improve this question

edited Sep 20 '16 at 7:43

Fabian Schmengler

54.9k21134350

asked Sep 19 '16 at 10:05

snh_nlsnh_nl

2,8931046104

add a comment | 

How can one get a quick list of sales/revenue per brand/manufacturer in Magento?

Something like

Brand Sales (minus returned/cancel)
brand 1 100 euro
brand 2 200 euro

Difficulty I see is: grouping by a product attribute. Correcting for returned and cancelled. And one could add a filter over time (from date - to date)

Any help appreciated

attributes sales reports

share|improve this question

edited Sep 20 '16 at 7:43

Fabian Schmengler

54.9k21134350

asked Sep 19 '16 at 10:05

snh_nlsnh_nl

2,8931046104

Brand Sales (minus returned/cancel)
brand 1 100 euro
brand 2 200 euro

share|improve this question

edited Sep 20 '16 at 7:43

Fabian Schmengler

54.9k21134350

asked Sep 19 '16 at 10:05

snh_nlsnh_nl

2,8931046104

share|improve this question

edited Sep 20 '16 at 7:43

Fabian Schmengler

54.9k21134350

asked Sep 19 '16 at 10:05

snh_nlsnh_nl

2,8931046104

edited Sep 20 '16 at 7:43

Fabian Schmengler

54.9k21134350

edited Sep 20 '16 at 7:43

Fabian Schmengler

54.9k21134350

asked Sep 19 '16 at 10:05

snh_nlsnh_nl

2,8931046104

asked Sep 19 '16 at 10:05

snh_nlsnh_nl

2,8931046104

1 Answer
1

active

oldest

votes

3

We've spent some time and that is the query we got:

SELECT 
 data_table.value AS Brand, SUM(data_table.amount) AS Sales
FROM
 (SELECT 
 EAOV.value,
 (SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded) AS amount
 FROM
 eav_attribute_option AS EAO
 INNER JOIN eav_attribute_option_value AS EAOV ON EAO.option_id = EAOV.option_id
 AND EAO.attribute_id = (SELECT 
 attribute_id
 FROM
 eav_attribute
 WHERE
 attribute_code = 'manufacturer')
 INNER JOIN catalog_product_entity_int AS CPEI ON CPEI.value = EAOV.option_id
 INNER JOIN sales_flat_order_item AS SFOI ON CPEI.entity_id = SFOI.product_id
 AND SFOI.price > 0
 AND SFOI.created_at >= '2015-01-01' AND SFOI.created_at < '2015-12-31'
 ) AS data_table
GROUP BY data_table.value
ORDER BY data_table.value ASC;

You may need to adjust the math:

SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded

We don't pretend this query is 100% accurate.

share|improve this answer

edited Sep 19 '16 at 12:03

answered Sep 19 '16 at 11:52

Neklo.comNeklo.com

2,2791518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You rock! I almost dare not asked. How would one add a DATE FROM and TO as a filter? (period of time)
  
  – snh_nl
  Sep 19 '16 at 11:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  We've updated the query. Please check.
  
  – Neklo.com
  Sep 19 '16 at 12:04
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "479"
;
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fmagento.stackexchange.com%2fquestions%2f137010%2fmagento-get-list-of-sales-revenue-per-brand-manufacturer%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

We've spent some time and that is the query we got:

SELECT 
 data_table.value AS Brand, SUM(data_table.amount) AS Sales
FROM
 (SELECT 
 EAOV.value,
 (SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded) AS amount
 FROM
 eav_attribute_option AS EAO
 INNER JOIN eav_attribute_option_value AS EAOV ON EAO.option_id = EAOV.option_id
 AND EAO.attribute_id = (SELECT 
 attribute_id
 FROM
 eav_attribute
 WHERE
 attribute_code = 'manufacturer')
 INNER JOIN catalog_product_entity_int AS CPEI ON CPEI.value = EAOV.option_id
 INNER JOIN sales_flat_order_item AS SFOI ON CPEI.entity_id = SFOI.product_id
 AND SFOI.price > 0
 AND SFOI.created_at >= '2015-01-01' AND SFOI.created_at < '2015-12-31'
 ) AS data_table
GROUP BY data_table.value
ORDER BY data_table.value ASC;

You may need to adjust the math:

SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded

We don't pretend this query is 100% accurate.

share|improve this answer

edited Sep 19 '16 at 12:03

answered Sep 19 '16 at 11:52

Neklo.comNeklo.com

2,2791518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You rock! I almost dare not asked. How would one add a DATE FROM and TO as a filter? (period of time)
  
  – snh_nl
  Sep 19 '16 at 11:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  We've updated the query. Please check.
  
  – Neklo.com
  Sep 19 '16 at 12:04
  

  
  
  
  

add a comment | 

3

We've spent some time and that is the query we got:

SELECT 
 data_table.value AS Brand, SUM(data_table.amount) AS Sales
FROM
 (SELECT 
 EAOV.value,
 (SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded) AS amount
 FROM
 eav_attribute_option AS EAO
 INNER JOIN eav_attribute_option_value AS EAOV ON EAO.option_id = EAOV.option_id
 AND EAO.attribute_id = (SELECT 
 attribute_id
 FROM
 eav_attribute
 WHERE
 attribute_code = 'manufacturer')
 INNER JOIN catalog_product_entity_int AS CPEI ON CPEI.value = EAOV.option_id
 INNER JOIN sales_flat_order_item AS SFOI ON CPEI.entity_id = SFOI.product_id
 AND SFOI.price > 0
 AND SFOI.created_at >= '2015-01-01' AND SFOI.created_at < '2015-12-31'
 ) AS data_table
GROUP BY data_table.value
ORDER BY data_table.value ASC;

You may need to adjust the math:

SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded

We don't pretend this query is 100% accurate.

share|improve this answer

edited Sep 19 '16 at 12:03

answered Sep 19 '16 at 11:52

Neklo.comNeklo.com

2,2791518

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You rock! I almost dare not asked. How would one add a DATE FROM and TO as a filter? (period of time)
  
  – snh_nl
  Sep 19 '16 at 11:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  We've updated the query. Please check.
  
  – Neklo.com
  Sep 19 '16 at 12:04
  

  
  
  
  

add a comment | 

We've spent some time and that is the query we got:

SELECT 
 data_table.value AS Brand, SUM(data_table.amount) AS Sales
FROM
 (SELECT 
 EAOV.value,
 (SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded) AS amount
 FROM
 eav_attribute_option AS EAO
 INNER JOIN eav_attribute_option_value AS EAOV ON EAO.option_id = EAOV.option_id
 AND EAO.attribute_id = (SELECT 
 attribute_id
 FROM
 eav_attribute
 WHERE
 attribute_code = 'manufacturer')
 INNER JOIN catalog_product_entity_int AS CPEI ON CPEI.value = EAOV.option_id
 INNER JOIN sales_flat_order_item AS SFOI ON CPEI.entity_id = SFOI.product_id
 AND SFOI.price > 0
 AND SFOI.created_at >= '2015-01-01' AND SFOI.created_at < '2015-12-31'
 ) AS data_table
GROUP BY data_table.value
ORDER BY data_table.value ASC;

You may need to adjust the math:

SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded

We don't pretend this query is 100% accurate.

share|improve this answer

edited Sep 19 '16 at 12:03

answered Sep 19 '16 at 11:52

Neklo.comNeklo.com

2,2791518

SELECT 
 data_table.value AS Brand, SUM(data_table.amount) AS Sales
FROM
 (SELECT 
 EAOV.value,
 (SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded) AS amount
 FROM
 eav_attribute_option AS EAO
 INNER JOIN eav_attribute_option_value AS EAOV ON EAO.option_id = EAOV.option_id
 AND EAO.attribute_id = (SELECT 
 attribute_id
 FROM
 eav_attribute
 WHERE
 attribute_code = 'manufacturer')
 INNER JOIN catalog_product_entity_int AS CPEI ON CPEI.value = EAOV.option_id
 INNER JOIN sales_flat_order_item AS SFOI ON CPEI.entity_id = SFOI.product_id
 AND SFOI.price > 0
 AND SFOI.created_at >= '2015-01-01' AND SFOI.created_at < '2015-12-31'
 ) AS data_table
GROUP BY data_table.value
ORDER BY data_table.value ASC;

SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded

share|improve this answer

edited Sep 19 '16 at 12:03

answered Sep 19 '16 at 11:52

Neklo.comNeklo.com

2,2791518

answered Sep 19 '16 at 11:52

Neklo.comNeklo.com

2,2791518

answered Sep 19 '16 at 11:52

Neklo.comNeklo.com

2,2791518