April 12, 2011 at 3:52 pm
count
low med high
ColA colB colC
country state city
col A col B col C have hierarchy with col c being the deepest level.
under
low med high,
count of the amount that is <100 $, >100 and <200 and >200
I have a field as field.amount value that gets me the actual amount.
but I need a count of how many times for a particular country state and city commbination the amount was low ie <100$.
Some places I do not have city at all so its country and city combo.
I tried using list
however it doesnt work when it comes to count of low/med/high , it gives me total count instead of broken up counts...
very confused ..please help.
Thanks
April 13, 2011 at 1:28 pm
YOu could do the counting in the query.
CREATE TABLE #test (Country CHAR(2), [STATE] CHAR(2), city VARCHAR(50), amount INT)
INSERT INTO #test
(
Country,
STATE,
city,
amount
)
SELECT
'US',
'FL',
'Miami',
100
UNION
SELECT
'US',
'FL',
'Tampa',
110
UNION
SELECT
'US',
'FL',
NULL,
52
UNION
SELECT
'US',
'FL',
'Miami',
99
SELECT
country,
STATE,
city,
Amount,
SUM(CASE WHEN amount < 100 THEN 1 ELSE 0 End) OVER (PARTITION BY country, city) AS count_of_low
FROM
#test AS T
DROP TABLE #test
In the report I'd use a hidden textbox that has a simple 1 or 0 if it is low and use a sum of that textbox to get the sum for each country/city combo.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2011 at 7:07 pm
Thanks for your reply, that looks good.
however still I am unable to do insert like that since that means I will have to manually insert data while it has to be built dynamically....any iideas would be appreciated...
is there a possibility that I can combine 3 unique combo of three different columns, in my query itself?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply