Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I think there're some issues with the code. Unfortunately I dont have tables and test data. So keep in mind, that I wrote the result more or less out of my head. Test carefully.</p> <p>To make it easier to read, I seperated the code into two steps. The first step gets the sum of sales and the second step calculates the total sales over all textures and builds the percentage. For your test, copy the first code two times into the second code, replacing the two occurences of <code>SALES_ONE_TEXTURE</code>. I just dont want to flood the answer more then necessary.</p> <p>Here's the first part for sum of sales for one texture (and month and year)</p> <pre><code>-- Use this as subselect. And you're right: you need it twice ( select s.TextureName, s.SubTextureId, o.year, o.month, sum(COALESCE(d.NetSales, 0)) NetSales from (select SubTextureId, TextureName from subtexture) as s join (select SubTextureId, ProductCode from products) as p on (p.SubTextureId = s.SubTextureId) left outer join (select ProductCode, OrderCode, NetSales from order_details) as d on (d.ProductCode = p.ProductCode) join ( select YEAR(o.PurchaseDateTime) year, MONTH(o.PurchaseDateTime) month, OrderCode from orders where o.PurchaseDateTime between '2013-11-01 00:00:00' and '2013-11-30 23:59:59' -- make use of an index if one exists ) as o on (o.orderCode = d.orderCode) group by 1,2,3,4 ) as SALES_ONE_TEXTURE; </code></pre> <p>And here the outer code to bring the numbers together:</p> <pre><code>-- The outer query select O.TextureName, A.year, A.month, A.NetSales, O.NetSales, cast(O.NetSales as float) / cast(A.NetSales as float) * 100.0 as percent from SALES_ONE_TEXTURE O -- replace here join ( select year, month, sum(NetSales) as NetSales from SALES_ONE_TEXTURE -- replace here group by 1, 2 ) as SALES_ALL_TEXTURES A on (A.year = O.year and A.month = O.month) </code></pre> <p>Remarks</p> <ul> <li><code>COALESCE</code> returns the first non-null value is often used to replace a null value with a default value. <code>NULLIF</code>does the opposite: Replace a value with null.</li> <li>Replacing a null that comes from an outer join does not work in all dialects on all RDBMS. You have to try if it works here. If not, you need to put the whole query in a subselect and do the <code>coalesce</code> replacement in the outer select</li> <li>I passed always year and month thru, where you passed the month. A month repeats every year, so only the combination is unique. I also changed the way to get the month. My way can make use of an index if one exists.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POHow to return "0" sales for a kind of subtexture from a left join of results that is null
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USjboi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to return "0" sales for a kind of subtexture from a left join of results that is null
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks jboi, It's late night here. I need to test these tomorrow, I trust you, you are all experts in here. Let me report back tomorrow. Have fun.
      singulars
      1. PO
      1. USuser3024062
    2. COThank you. Lets see how it works tomorrow.
      singulars
      1. PO
      1. USjboi
    3. COI have followed your hints, But one thing is that I can't have a subtexture that has zero number of sales to be displayed. Say SubTexture A has sales of 1000, type b has 2000 and type c has 0, type c has never come up
      singulars
      1. PO
      1. USuser3024062
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload