Count Where
Hey,
Our client has a large number of sites. Each site can order items from any of 20 different suppliers.
Our client wishes to see how many sites are purchasing over $100 worth of items from each supplier. They also plan on running more advanced filters and queries on this data, so this isn't something I can do for them with a freehand SQL query each time they need it.
Traditionally, this is how the SQL query would look:
- SELECT a.Supplier, a.Date, COUNT(b.Site)
- FROM Sales a
- LEFT JOIN (
- SELECT Site, Supplier, Date, SUM(Amount)
- FROM Sales
- GROUP BY Site, Supplier, Date
- -- WHERE -- Other filters would go here, such as by product
- HAVING SUM(Amount) > 100 -- The "100" could potentially be changed using a parameter
- ) b
- ON a.Supplier = b.Supplier AND a.Date = b.Date
- GROUP BY a.Supplier, a.Date
I can only figure out how to do this with sub-totals. But this makes VERY ugly looking reports that are difficult to manage and can't build charts on. Is there another way to do this?
I feel like it might work best with a Sub-Query, but I can't figure out how to filter by the SUM on a per-site basis while using count or count-distinct.
I tried to use a calculated field to count the number of sites in the sub-query, which would have worked if Yellowfin allowed me to hide the sub-query in its entirety, and thus eliminate all the extra rows it generates.
Thanks
The same question
Hi Johnathon,
Thank you for getting in touch. I think that the best way to do this will be through the use of a custom SQL filter on the concatenation of supplier and site.
Essentially the query will return a list of concatenated(site,supplier) where the sum of their purchases are > 100.
In the example below I have the foundational data-set; a list of booking methods (representing supplier), countries (representing sites), invoiced amounts (filtered by >100k), and the view level calculated field (concat booking/country initials)
Once I have this report, I grab the Freehand SQL statement, and remove all but the concatenated field from the select clause:
Now that I have the statement I want, I can create a new report using just the supplier (booking), and filtering the results on my concatenated field.
Please let me know if this works for you.
Regards,
Nathan
Hi Johnathon,
Thank you for getting in touch. I think that the best way to do this will be through the use of a custom SQL filter on the concatenation of supplier and site.
Essentially the query will return a list of concatenated(site,supplier) where the sum of their purchases are > 100.
In the example below I have the foundational data-set; a list of booking methods (representing supplier), countries (representing sites), invoiced amounts (filtered by >100k), and the view level calculated field (concat booking/country initials)
Once I have this report, I grab the Freehand SQL statement, and remove all but the concatenated field from the select clause:
Now that I have the statement I want, I can create a new report using just the supplier (booking), and filtering the results on my concatenated field.
Please let me know if this works for you.
Regards,
Nathan
Hi Johnathan,
Have you had any luck in resolving this?
Regards,
Nathan
Hi Johnathan,
Have you had any luck in resolving this?
Regards,
Nathan
Hi Jonathan, we're under the impression this has resolved the issue, so going to flag as answered.
Please let us know in the coming days if this is not the case.
Regards,
David
Hi Jonathan, we're under the impression this has resolved the issue, so going to flag as answered.
Please let us know in the coming days if this is not the case.
Regards,
David
Replies have been locked on this page!