SQL COUNT

I am trying to calculate the the number of distinct items and its sum. Its partially works. However, I got 1 problem I just don't know  How to go around it.

I have
Reschedule Location Fee $5

and I may have
Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee

Once i ran the query I get to rows

Reschedule Location Fee  1 $5
and I get this
Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee|Reschedule Location Fee 7 $35

What I need is to combine this 2 records into one which will say

Reschedule Location Fee 8     $40

SELECT
                                count(1) as cnt,ProductList,sum(isNUll(T.price,0)) as price
                            FROM (

                            SELECT COUNT(1) as cnt,SUM(isNULL(T.amount,0)) as price,
                             (
                                    SELECT TOP 1 LAS.availStateCode
                                    FROM lineitems LI
                                        INNER JOIN lkup_availState LAS  ON LI.officeStateKey = LAS.availStateKey
                                    WHERE O.orderKey = LI.orderKey
                                ) AS availStateCode
                                , LEFT(ProductList, LEN(ProductList) - 1) AS ProductList
                            FROM
                                Trans T
                                INNER JOIN Users U  ON T.userKey = U.userKey
                                LEFT OUTER JOIN (
                                    Orders O
                                    INNER JOIN Users UO  ON O.userKey = UO.userKey
                                ) ON T.orderKey = O.orderKey

                                CROSS APPLY (
                                    SELECT LI2.description + '|'
                                    FROM lineitems LI2
                                    WHERE O.orderKey = LI2.orderKey
                                    ORDER BY LI2.description
                                    FOR XML PATH('')
                                ) AS Cross2(ProductList)

                            WHERE T.TransDate BETWEEN '6/29/2015' AND '6/30/2015' and T.void = 'N' 
                            GROUP BY ProductList,O.orderKey
                            
                            
                            )T
                            WHERE availStateCode = 'MD'
                            GROUP BY T.ProductList,T.availStateCode
                            Order by T.productList

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
The best method of asking such a question is to provide:
1. sample data
2. the expected result (related to the sample)

Please remember we don't know your tables or data, so understanding a query without that info is complex to say the least.

==
The final GROUP BY will produce a row for each unique combination of (T.ProductList,T.availStateCode) so if you are getting 2 rows, but only want one, you may have to alter that group by to just:

GROUP BY T.ProductList

but I would really prefer to work with sample data to offer more

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Waiting for the sample data to help you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.