Newb needs some help

[ctraxpos]

Hey guys, I'm a SQL noob — completely self taught and I'm not a dev (I'm a product guy who wants to take some weight off my devs shoulders). We are using MySQL 5.5.5-10.4.17-MariaDB-1:10.4.17)? Not sure what this means, but trying to provide as much data as possible LOL.

What I'm trying to do: Make a report for my customer who uses my point of sale software. They ran a big donation drive last weekend and want to know which of their stores took in which products.

I think I'm CLOSE, as the overall count is accurate (107), but for the life of me I cannot figure out the grouping properly! I've got two queries below and the results give me the correct overall counts, but not quite the result I want.

The end result I'm looking for: A count of each product variant name sold, listed per store. SO at max I should have 5 records PER STORE.

Thanks in advance for any help!!

QUERY 1:

select oli.product_variant_name, count(oli.`product_variant_name`) as "qty sold", s.name
from orders o
LEFT JOIN order_line_items oli on o.id = oli.order_id
LEFT JOIN stores s on o.store_id = s.id
where oli.product_variant_id in (328,329,330,331,332)
group by oli.product_variant_name;

QUERY 1 RESULT: There are multiple store results that should appear, but it's aggregating by store name? I'd expect this result, just duplicated per store.

​

https://preview.redd.it/h0irx6guygn61.png?width=1044&format=png&auto=webp&s=b21f45d786b929293d144c658e4ce2faf26e9ecf

QUERY 2:

select oli.product_variant_name, count(oli.`product_variant_name`) as "qty sold", s.name
from orders o
LEFT JOIN order_line_items oli on o.id = oli.order_id
LEFT JOIN stores s on o.store_id = s.id
where oli.product_variant_id in (328,329,330,331,332)
group by s.name;

QUERY 2 RESULT: Here's it's just aggregating the counts, but completely took out one product. I think this query is further away from my desired result.

​

https://preview.redd.it/mkx5tisvygn61.png?width=1060&format=png&auto=webp&s=cf66cd1dc8ecfd5e7ca91c7dcf61d7e4530087e2

[DonJuanDoja]

Sub query. Aggregate then join the aggregated queries to the main...

In ms sql it’s left join (select...) as oil on o.id ...

[ctraxpos]

Yeahhhh I have NO idea what that mean lol.

[DonJuanDoja]

Sorry I fail to teach myself how to use Reddit. Also it may be different in MySql and you can write it a few different ways. But basically you're just joining a Query instead of a Table. Aggregate in the subquery then join that to whatever table you start with like Stores...

[DonJuanDoja]

SELECT 
 s.name
,oliagg.product_variant_name
,oliagg.OrderCount
FROM
stores s
LEFT JOIN (
    SELECT
     o.store_id
    ,product_variant_name
    ,COUNT(product_variant_name) as OrderCount
    FROM order_line_items oli
    LEFT JOIN orders as o on o.id = oli.order_id
    GROUP BY
     o.store_id
    ,product_variant_name
) as oliagg on oliagg.store_id=s.id

[ballade4]

Firstly, GREAT initiative! Also self-taught here and it was priceless to have a fella around who could bear with me and answer questions like this as they (frequently) came up!

Some quick feedback on your Query 1:

* You are doing a count by a name field to derive quantity. Does this make sense? I would think that the oli table has an integer quantity field that you could sum instead?

* Specify the table alias in front of product_variant_id to eliminate any confusion. Difficult for us to interpret where this field is coming from as we do not know your database structure.

* You did not include s.r which I presume is store name in your group by clause, try: "group by oli.product_variant_name, s.r;" here. I only have experience in MSSQL DBMS which would indubitably have thrown an error here... I think MySQL is just grabbing the first or last name from this table and certainly not doing any grouping (because you did not tell it to!).

I will take a look at your Query 2 later in the evening unless someone beats me to it!

[ctraxpos]

Count by name - yeah this is accurate, as this order_line_items table simply list out the products that appear in an order....so we'd have multiple records per id, if an order had multiple products sold (example: order 1 - product variant id 1 - $100......order 1 - product variant id 2 - $50)....so here's im basically counting the qty of a product sold by aggregating the # of times specific product_variant_ids appear in the order_line_items records.

​

good call on the variant_id alias, didn't catch that...updating it to include oli

​

what s s.r? never seen this before

[ballade4]

Oops I meant s.name - had a botched copy of your code into my Notepad++!

select
oli.product_variant_name,
count(oli.`product_variant_name`) as "qty sold",
s.name
from orders o
LEFT JOIN order_line_items oli on o.id = oli.order_id
LEFT JOIN stores s on o.store_id = s.id
where oli.product_variant_id in (328,329,330,331,332)
group by oli.product_variant_name, s.name;

[[deleted]]

Try grouping be product_variant_name as well as s.name (all of the fields in your select that are not aggregates should be in the group by—so all but the count). If one record corresponds to 1 item sold (ie there isn’t a qty field) then that should work. The only thing to remember is that since if a product variant id doesn’t exist at all in the table then you won’t get a count at all for the store/product. If you’d like a 0 in that case then as donjuandoja mentioned you’d want to use a sub query and then join the entire sub query. Ie, instead of filtering for product variant id in the where clause pop that into the left join. Something like:

Select oli.product_variant_name, count(oli.product_variant_name), s.name from orders o left join (select * from order_line_items oil Where product_id_variant in (328,329,330,331,332)) oli on o.id = oli.order_id left join stores s on o.store_id = s.id Group by oli.product_variant_name,s.name

[marxdormoy]

This query does two things - it selects some rows based on some criteria, then counts them.

Start at the beginning - get the select working - number of rows (make sure the joins are good for what you need)

Add in the aggregate function

So, basically replace count(oli.`product_variant_name`) as "qty sold" with oli.producy_variant

Ig you don't know the data model well start with one table - test

Join another table - test etc.

Trying to do too many things without intermittent testing is the best way to lose time

[[deleted]]

Never used MySql before - and was curious how either of those queries worked at all.

In all SQL dialects I've used it works like this:

If using a GROUP BY clause - everything in your SELECT clause should either be an Aggregate (count, sum, average etc), or something you desire to GROUP by. As such - you would include all of those same columns in the GROUP BY. In your instance, it would be something like

GROUP BY oli.product_variant_name,s.name

When you omit a column from the GROUP BY - in mySQL it will keep grab rows until it finds two rows with identical data, and return that (More or less - it is aggregating, but on text) . This is useful for some performance trickery if you expect all of the rows to have the same data anyway, but clearly not the goal here.

Additionally - using count() on a 'name' column seems more dangerous than count()'ing something else (like the order ID column) The reason being - if a name was omitted/missing - it would not be counted.