May 14, 2008 at 1:58 pm
I have an existing table of transactions from our retail stores and I'm trying to produce a summarization of the data. For each store, I have a row that shows total_sales, total_customers, and then individual columns showing how many customers paid by cash, check, VISA, MasterCard, Discovery Card, Gift Card etc.
I need to come up with a view which sums all of the VISA, MasterCard, and Discovery cards together as a single number. As an example of what my table currently looks like:
Store Cust_Count Cash Check VISA MasterCard Discover GiftCard
001 210 110 35 30 25 5 5
What I want to end up with is:
Store Cust_Count Cash Check Cards
001 210 110 35 65 <---- SUM( all card types )
T-SQL is complaining at me about not being able to perform an aggregate function on an expression containing an aggregate or subquery. I can't quite seem to figure out a way around this short of stuffing all of the intermediate results into a temp table and then summing them together.
Can anyone point me toward a way of accomplishing this?
Thanks!
May 14, 2008 at 2:10 pm
Please post the code you've tried.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 14, 2008 at 2:11 pm
Knowing how your tables were set up would help with giving you a much more specific answer, but it sound to me that you need some kind of case statement.
Something like:
select
storeID,
count(distinct CustID) as Customers,
sum(case when tender='Cash' then 1 else 0 end) as cash,
sum(case when tender='Visa' then 1 else 0 end) as Visa,
sum(case when tender='MasterCard' then 1 else 0 end) as MasterCard,
sum(case when tender='Discover' then 1 else 0 end) as Discover,
sum(case when tender='GiftCard' then 1 else 0 end) as GiftCard,
sum(case when tender in ('visa','MasterCard','Discover') then 1 else 0 end) as CreditCard
from transactions
group by storeID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2008 at 2:44 pm
Do you truly have a table like the following?
Store Cust_Count Cash Check VISA MasterCard Discover GiftCard
001 210 110 35 30 25 5 5
One could infer from you post that you don't actually have that table, but if you do, the solution is simple:
SELECT Store, Cust_Count, Cash, Check, (VISA + MasterCard + Discover + GiftCard) as Cards
FROM YourTable
May 14, 2008 at 2:53 pm
Wow! Thanks for the rapid replies Matt and Jason!
I continued playing around with T-SQL code until I discovered that the SUM( ) function will allow me to SUM multiple fields at once! (I guess I always thought that it would only work on a single column of data...but I tried it using multiple column names and it worked!)
Here is what I found to do the trick:
select
store,
SUM( Store_CC )As CustCount,
SUM( Store_Sales ) As Sales,
SUM( VISA + MasterCard + Discover + AMEX + GiftCard ) As AllCards
FROM TLog_Store_Summary
where store = 1
and dayid = 13717
Group By store
Matt - you were pretty close, but the suggestion you offered would result in a count of 1 if there were any values in the VISA/MasterCard/GiftCard column...what I needed was to add up the values of those columns for a total of all of them. The SUM for the AllCards result column is what I was looking for.
Thanks for your help.
May 14, 2008 at 2:59 pm
Oops! I forgot to explain that the TLog_Store_Summary table currently contains hourly data, so in order to summarize it with a view into a set of "daily" numbers by store, I had to SUM( ) all of the hourly buckets into daily ones where all of the electronic cards (no matter the type) were summed together for a single number.
May 14, 2008 at 3:04 pm
Thanks for the clarification Larry.
And based on that info, your solution looks just like what I would have suggested. 🙂
Cheers!
May 15, 2008 at 1:08 pm
Larry Kruse (5/14/2008)
Matt - you were pretty close, but the suggestion you offered would result in a count of 1 if there were any values in the VISA/MasterCard/GiftCard column...what I needed was to add up the values of those columns for a total of all of them. The SUM for the AllCards result column is what I was looking for.Thanks for your help.
Yup...I misread that you were looking for a $ total, not a tally of how many customers paid using that tender.
You're welcome - sounds like you found what you needed anyway...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply