We help IT Professionals succeed at work.

SQL Query to sum values on tickets where one or more ticket lines contain a list of values, grouped by that list of values.

AD1080
AD1080 asked
on
209 Views
Last Modified: 2017-04-08
Hi,

I have a typical invoice table and a corresponding invoice lines table.

Certain invoices have invoice lines for a specific list of item numbers that I am interested in.  

When an invoice has a line item with one of the items from the list, I want to get the total unit sales for that entire invoice including all lines, not just the line with the item_no from my list.

The result set I am looking for would be grouped by this list of items that I interested in.  

In the example data, if the list of items I am interested in is (1,4) then the result set would be:

ITEM_NO, UNITS
1,14
4 ,24

Because ITEM_NO 1 appears in tickets where TICKET_NO = 1 AND TICKET_NO = 2, and the sum of all UNIT_SALES for these 2 tickets is 14.

Because ITEM_NO 4 appears in tickets where TICKET_NO = 3 AND TICKET_NO = 4, and the sum of all UNIT_SALES for these 2 tickets is 24.

Thanks in advance for you help on this.
Example-Data.xlsx
Comment
Watch Question

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
sorry, wrong answer
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
select distinct t1.ITEM_NO, ca.all_units
from YourTable t1
cross apply (
            select sum(UNIT_SALES) all_units
            from YourTable t2
            where TICKET_NO in (
                select TICKET_NO
                from YourTable t3
                where t3.ITEM_NO = t1.ITEM_NO
                )
            ) ca
where t1.ITEM_NO in (1,4)
    

Open in new window

Author

Commented:
Thanks very much.  I think this is working perfectly.
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
Here is the query:

select b.ITEM_NO, sum(a.unit_sales)
from 
(
  select TICKET_NO, sum(unit_sales) unit_sales
  from @temp_table
  group by TICKET_NO
) a
left outer join (
select ticket_no, ITEM_NO
from @temp_table
where ITEM_NO in (1, 4)
) b on a.TICKET_NO = b.TICKET_NO
group by b.ITEM_NO

Open in new window


Please substitute appropriate table names. If you plan to put 1 and 4 in a table, you may place a SELECT statement in place of comma separated list above.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
I should point out that if you have a master table of items, then I would run it from that table.

Assuming those item numbers refer to unique rows in a table of PRODUCTS then something like this

select t1.ITEM_NO, ca.all_units
from PRODUCTS t1
cross apply (
            select sum(UNIT_SALES) all_units
            from YourTable t2
            where TICKET_NO in (
                select TICKET_NO
                from YourTable t3
                where t3.ITEM_NO = t1.ITEM_NO
                )
            ) ca
where t1.ITEM_NO in (1,4)

Open in new window

This way you should not need to use "select distinct"
Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
This worked well, and processed in under 30 seconds.  I tried using the Cross Apply approach before this, and with the data set I'm working, it was still trying to execute after 15 minutes.  

I tried something similar but without defining CTE's the way you have done it, and it would never finish processing.  Somehow defining the CTS's first made it work.

I'm sure the Cross Apply may work in other situations.   The actual key for tickets in our system is a combination of 4 primary keys, so this may be affecting performance.  

Thanks again!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.