September 28, 2016 at 4:13 am
Hi,
I have a table and I am having a hard time understanding how to write the code. :w00t:
The final user needs to be able to choose the following.
one or many "population"
one or many indicators, meaning he can choose only ind_1, ind_2 or a combination of the columns, the original table has more than two columns.
depending on what the final user choose i need to calculate the amount sent taking in consideration where the indicator is 1 or 0.
my problem is
1) how can i choose the population of the indicator.
if i choose population a, i will subdivide the users in ind_1 into two groups, good group where they have a 0 and bad group where they have a 1, and i will calculate the spending.
but if the user choose more than one indicator, how do i tell the table what to calculate, the more indicators i have the the more options to combine them.
this is the sample table
thanks
create table panel
(
population nvarchar(2)
,useridint
,ind_1 smallint
,ind_2smallint
,amountspent float
)
insert into panel (population, userid, ind_1, ind_2 ,amountspent) values
('a' ,1, 1, 0, 1110)
,('a' ,2, 0, 1, 1372)
,('a' ,3, 1, 0, 1331)
,('a' ,4, 0, 0, 1921)
,('a' ,5, 1, 0, 1559)
,('a' ,6, 1, 0, 1166)
,('a' ,7, 1, 1, 1128)
,('a' ,8, 1, 0, 1597)
,('a' ,9, 0, 0, 1185)
,('a' ,10, 0, 0, 1900)
,('a' ,11, 0, 1, 1007)
,('a' ,12, 0, 1, 1183)
,('a' ,13, 0, 1, 1509)
,('b' ,14, 1, 0, 1226)
,('b' ,15, 1, 0, 1428)
,('b' ,16, 0, 0, 1988)
,('b' ,17, 1, 1, 1778)
,('b' ,18, 1, 0, 1561)
,('b' ,19, 1, 1, 1448)
,('b' ,20, 1, 0, 1340)
,('b' ,21, 1, 1, 1682)
,('b' ,22, 1, 1, 1744)
,('b' ,23, 0, 0, 1072)
,('b' ,24, 1, 0, 1580)
,('b' ,25, 0, 0, 1819)
,('b' ,26, 1, 0, 1785)
,('b' ,27, 0, 1, 1240)
,('c' ,28, 0, 1, 1947)
,('c' ,29, 1, 1, 1374)
,('c' ,30, 0, 0, 1031)
,('c' ,31, 1, 0, 1412)
,('c' ,32, 0, 0, 1447)
,('c' ,33, 1, 0, 1146)
,('c' ,34, 1, 0, 1011)
,('c' ,35, 0, 0, 1200)
,('c' ,36, 0, 1, 1974)
,('c' ,37, 0, 0, 1898)
,('c' ,38, 0, 1, 1856)
,('c' ,39, 0, 1, 1810)
,('c' ,40, 1, 0, 1059)
,('d' ,41, 0, 1, 1020)
,('d' ,42, 0, 0, 1273)
,('d' ,43, 0, 1, 1569)
,('d' ,44, 0, 1, 1288)
,('d' ,45, 1, 1, 1257)
,('d' ,46, 0, 1, 1245)
,('d' ,47, 0, 0, 1094)
,('d' ,48, 1, 0, 1324)
,('d' ,49, 0, 1, 1851)
,('d' ,50, 0, 1, 1776)
,('d' ,51, 1, 0, 1657)
,('d' ,52, 1, 1, 1375)
,('d' ,53, 0, 1, 1262)
,('d' ,54, 1, 0, 1021)
,('d' ,55, 1, 0, 1582)
,('d' ,56, 1, 0, 1068)
,('d' ,57, 0, 1, 1617)
,('d' ,58, 1, 1, 1746)
select * from panel
September 28, 2016 at 9:21 pm
What would the expected results your sample data look like from your example above? Please don't describe it. Just draw us a picture. 🙂
You cannot choose columns to be included without writing dynamic SQL.
If all you are doing is subdividing the indicator columns based on zero and 1 amounts, that can be done fairly easily, and I am tempted to say just do the same for all amounts and let the user interface select which results they want to display. The I/O time will be same and the calculation time shouldn't be prohibitive.
I am unsure of what you intend to do if both ind_1 and ind_2 are involved. Could you please show some sample results for that as well?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 29, 2016 at 12:21 am
I guess it is hard to understand. but i have written a sample of code.
this sample is good if the customer choose population_1 that means
ind_1 > 0
ind_2 > 0
or population_2 that means
ind_2 > 0
ind_3 > 0
or any unique population, my problem is that i want to provide a drop down where the customer will choose population_1 , population_2, population_3, population_4, etc, or any combination of the list.
i thought about using if, but talking in consideration that i have 10 different population, the number of combinations is a bit more code that i think it should be written for every possibility.
with population_1
as
(
select userid from mysample where ind_1 > 0
union
select userid from mysample where ind_2 > 0
), sumas
as
(
select populations, count(userid) as NumberofUsersBad, sum(amountspent) as TotalSpendBad
from mysample
where userid in (select userid from population_1)
group by populations
), panel
as
(
select populations, count(userid) as NumberofUsersTotal, sum(amountspent) as TotalSpend
from mysample
group by populations
)
select p.populations, sum(p.NumberofUsersTotal) as NumberofUsersTotal,
sum(s.NumberofUsersBad) as NumberofUsersBad,
sum(p.TotalSpend) as TotalSpend,
sum(s.TotalSpendBad) as TotalSpendBad
from panel p join sumas s on p.populations = s.populations
group by p.populations
September 29, 2016 at 6:04 am
You have some basic errors in the DDL you posted. A table has to have a key, but you have none. And no way to ever have a key because of all the nulls. We do not use numerics for identifiers, encodings, etc. because we do no math on them. Currency amounts have to be expressed as decimal, not float. This is not a matter of just good design, it is a matter of the law. Read the GAAP or EU regulations concerning computations with money. So first let us rewrite your DDL and make it into a real table.
CREATE TABLE Panels
(user_id CHAR(5) NOT NULL PRIMARY KEY,
population_name CHAR(2) NOT NULL,
something_flag_1 CHAR(1) NOT NULL CHECK(something_flag_1 IN ('G', 'B')),
something_flag_2 CHAR(1) NOT NULL CHECK(something_flag_2 IN ('G', 'B')),
expense_amt DECIMAL (10,2) NOT NULL CHECK (expense_amt >= 0.00)
);
The next basic error is you want to use the database layer to do the work that should be done in a presentation layer. Compute all of your totals for the flags, and pass it up to the other tiers in your architecture. Let them decide which ones to keep or modify.
Your mindset is still stuck in a monolithic architecture instead of tiered architectures. Each tiered is a particular job; next you to be thinking there such a thing as a drop-down list in a query
SELECT population_name,
SUM(CASE WHEN something_flag_1 = 'G'
THEN expense_amt) ELSE 0.00 END)
AS good_expense_amt_tot,
SUM(CASE WHEN something_flag_1 = 'B'
THEN expense_amt) ELSE 0.00 END)
AS bad_expense_amt_tot,
SUM(CASE WHEN something_flag_2 = 'G'
THEN expense_amt) ELSE 0.00 END)
AS good_expense_amt_tot,
SUM(CASE WHEN something_flag_2 = 'B'
THEN expense_amt) ELSE 0.00 END)
AS bad_expense_amt_tot,
...
FROM Panels
WHERE population_name = @in_population_name
GROUP BY population_name;
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 29, 2016 at 6:28 am
Hi,
Thanks for your reply, but I just wrote an example.
the table i need to work on, it is not define by me, neither i can define it or change it, and to make matter worse, it doesn't have keys. i just wrote a sample, so it doesn't really matter.
and still that doesn't answer how can i actually choose the population i need to work on without writing all the possibilities...
but thanks 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply