# 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
LEFT OUTER JOIN (
Orders O
) 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
``````
LVL 19
###### Who is Participating?

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.

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