query for calculating sales total for subcategory and per item

I have a table xyz with fields of category, subcategory, item_no, sales.
category1, subcategory1, 1234, $55.00
category1, subcategory1, 1234, $42.92
category1, subcategory1, 1238, $18.72
.....
I would like to have a report listing of

category, subcategory, item_no, no of sales per item, total$/item, no of sales per subcategory, total$/subcategory.
category1, subcategory1, 1234, 2, $97.92, 4, $132.00
category1, subcategory1, 1238, 1, $18.72
category1, subcategory1, 323, 1, $15.36

category2, subcategory2, 222, 1, $18.48, 1, $18.48
...


The following query will give me some but not all what I want. I am missing 'no of sales per subcategory' and 'total $ per subcategory'. How do I modify the query to get the report I want?

$query = "SELECT * ,SUM(sales) as stotal, COUNT(*) as total FROM xyz GROUP BY category, subcategory, item_no order by category ASC, subcategory ASC, item_no ASC, stotal DESC";

Thanks for any help in advance.

 

You can't have your individual records and your totals too. At least not in the same query. You can either get all your records and then programmatically calculate the subcategory totals or you can create a query to calculate them for you.

To programmatically get them you would need variables to keep track of the current subcategory, the record count for that subcategory, the total of that subcategory and an sql statement which orders your query by the subcategory:

$subcount=0;
$subtotal=0;
$subcategory="";

$query="SELECT * FROM xyz ORDER BY subcategory;"

PHP:

Then you would loop through your records, compare the $subcategory variable to the subcategory of the record you are working with. If they are different you would spit out the values in the $subcount and $subtotal, reset them to 0 and set the $subcategory variable to that new subcategory. If they are the same, you would add that record's sales to the $subtotal variable and increment the $subcount variable.

To just get the totals in a query you would pull just the data you wanted and use GROUP BY in your query like this:

$query="SELECT subcategory, SUM(sales) AS stotal, Count(sales) AS total FROM xyz GROUB BY subcategory;";

PHP:

 

@aayybb, please share table structure, let us try if its possible via one query...

 

The table has id, category, subcategory, item_no, and sales. I was thinking about using join query inside a query. Not good at it.

 

That's the way
These are called nested joins - try Google for nested queries.
Something like that:

SELECT * FROM
(SELECT A.*,
SUM(sales) as stotal, 
COUNT(*) as total 
FROM xyz A 
GROUP BY 
category, 
subcategory, 
item_no 
order by category ASC, subcategory ASC, item_no ASC, stotal DESC
) a
NATURAL JOIN
(SELECT subcategory,SUM(sales) AS TOTAL_SALES_PER_CATEGORY
FROM xyz B
GROUP BY subcategory
ORDER BY subcategory ASC
) b;

Code (markup):

I left the pleasure of extending and optimizing the query to you
Best regards,
Nick

 

Thank you very much.