March 15, 2012 at 10:26 am
Hello
I have a data structure like this:
Entities 1 - X EntitieDefinition 1 - X Data
I have a query, that for each different entity, gives me the number of rows in the data table, the sum of the values (column with value) and the percentage of the sum of the value of all the rows
Name - Qt - Total - Percentage
Entity1 - 3 - 120 - 50%
Entity2 - 1 - 60 - 25%
Entity3 - 4 - 30 - 12.5%
Entity4 - 1 - 30 - 12.5%
Now i have to build a new query, that joins some of the entities in a new one and get the new totals and the new percentage.
For instance, if the rule in the sample was to join the odd and the even entities, what i need to get is something like this:
EntityEven - 7 - 150 - 62.5%
EntityOdd - 2 - 90 - 37.5%
The current query:
SELECT DISTINCT
EntName = ENT.nameEnt,
QT = COUNT(ED.id),
TOTAL = SUM(ED.cost),
PERC = (SUM(ED.cost) / SELECT SUM(cost) FROM EntitiesData)
FROM Entities ENT
INNER JOIN EntitiesDef ETD ON ENT.id = ETD.entity
INNER JOIN EntitiesData ED ON ETD.id = ED.def
ORDER BY EntName
GROUP BY ENT.nameEnt
I was looking to the Case When, but the grouping doesn't work because of the original name...
How can i achieve this, should i create a temp table and then work over this temp table?
Thanks
March 15, 2012 at 10:43 am
It's too late today for doing all preparation work. Please follow here:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 15, 2012 at 10:57 am
Thanks for the tip, but i know that i should added some sample data, but my tables are a little bigger than the fields that i refer here, to do that i need to cut some columns, adapt the joins and other stuff that i can't recall right now...
I thought that by put the main fields in the sample was sufficient to expose my problem and what I'm trying to do and the problem that I'm facing.
I don't need the code, just some tips to help me...
Thanks
March 15, 2012 at 11:16 am
You don't need to put the DDL for all table, just make-up to contain columns used in the query. The same for data-sample. Few rows will do which can be rolled to expected results...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply