maybe there are other ways to write what you want, just a simpler way...

Solved

Posted on 2011-10-12

The goal is to display any dictatorid with more than one last name assigned to it along with the total of the billing lines for each of the multiple last names.

This does not quite work:

SELECT distinct dictatorid, dictatorlastname,reportperiod, cnt,sumx

FROM (SELECT dictatorid,

dictatorlastname,

reportperiod,

COUNT(DISTINCT dictatorlastname) OVER (PARTITION BY dictatorid) cnt,

SUM(BILLINGLINES) OVER (PARTITION BY dictatorlastname) sumx

FROM ichart.transactions

WHERE reportperiod = '201109')

WHERE cnt > 1 and reportperiod = '201109'

ORDER BY dictatorid ASC;

Gives me:

DICTATORID DICTATORLASTNAME REPORTPERIOD CNT SUMX

---------- ---------------------------------------- ------------ ---------------------- ----------------------

1893NOC Felder, K 201109 2 6340

1893NOC Zavatsky, J 201109 2 7985

The actual sum of billing lines for 1893NOC in reportperiod '201109' is 6669. Not sure what I am doing wrong. The goal is to display any dictatorid with more than one last name assigned along with the total of the billing lines for each of the multiple last names.

Can some one help me tweak this?

Thanks in advance!

This does not quite work:

SELECT distinct dictatorid, dictatorlastname,reportper

FROM (SELECT dictatorid,

dictatorlastname,

reportperiod,

COUNT(DISTINCT dictatorlastname) OVER (PARTITION BY dictatorid) cnt,

SUM(BILLINGLINES) OVER (PARTITION BY dictatorlastname) sumx

FROM ichart.transactions

WHERE reportperiod = '201109')

WHERE cnt > 1 and reportperiod = '201109'

ORDER BY dictatorid ASC;

Gives me:

DICTATORID DICTATORLASTNAME REPORTPERIOD CNT SUMX

---------- --------------------------

1893NOC Felder, K 201109 2 6340

1893NOC Zavatsky, J 201109 2 7985

The actual sum of billing lines for 1893NOC in reportperiod '201109' is 6669. Not sure what I am doing wrong. The goal is to display any dictatorid with more than one last name assigned along with the total of the billing lines for each of the multiple last names.

Can some one help me tweak this?

Thanks in advance!

19 Comments

maybe there are other ways to write what you want, just a simpler way...

SUM(BILLINGLINES) OVER (PARTITION BY DICTATORID ) sumx

but then you won't get values for each name.

maybe I'm not understanding what you're really looking for

```
SAMPLE:
DICTATORID DICTATORLASTNAME BILLINGLINES REPORTPERIOD
---------- ---------------------------------------- ---------------------- ------------
5774BRC Kindred, J 20 201109
5774BRC Monaco, B 47 201109
5774BRC Monaco, B 38 201109
3972BRC Sledge, E 26 201109
5774BRC Kindred, J 47 201109
5774BRC Kindred, J 38 201109
3972BRC Sledge, E 26 201109
EXPECTED RESULT OF QUERY
sumx cnt
5774BRC Monaco, B 85 2
5774BRC Kindred, J 105 3
```

I don't see a problem

the column order is different and you took out the period but the other content is the same

SELECT DISTINCT dictatorid, dictatorlastname, sumx, cnt

FROM (SELECT dictatorid,

dictatorlastname,

reportperiod,

COUNT(DISTINCT billinglines) OVER (PARTITION BY dictatorid, dictatorlastname) cnt,

SUM(billinglines) OVER (PARTITION BY dictatorlastname) sumx

FROM transactions

WHERE reportperiod = '201109')

WHERE cnt > 1

ORDER BY dictatorid ASC, cnt;

select DICTATORID, DICTATORLASTNAME, sum(BILLINGLINES) sumx, count(1) cnt

group by DICTATORID, DICTATORLASTNAME

having count(1) > 1

or is it too simple?

select DICTATORID, DICTATORLASTNAME, sum(BILLINGLINES) sumx, count(1) cnt

from ichart.transactions

group by DICTATORID, DICTATORLASTNAME

having count(1) > 1

Select DICTATORID, DICTATORLASTNAME, sum(BILLINGLINES) sumx, count(distinct billinglines) cnt

from ichart.transactions

group by DICTATORID, DICTATORLASTNAME

having count(distinct billinglines) > 1

I want to exclude dictatorid's that have only a one-to-one match with a dictatorlastname.

The goal is to present dictatorid's that are used with more than one lastname (this is incorrect data that will have to be corrected.), along with the count of how many times the combination is used and a sum of the billing lines for each combination.

If a dictatorid appears at all, it should appear at least twice, accompanied by different last names, with a count of at least 2, with a sum of the billing lines for each of the last names for the over-used dictatorid.

what is "that" ? There are 4 queries above since you lasted posted.

2 of the queries produce what you asked for given the data.

If they aren't correct, Can you expand your sample a little so we can see the pattern?

not quite, it returns extra row for Sledge which, according to the sample, isn't correct

my followup to that one does match the sample, but possibly the sample is full enough to reveal problems in either of the queries I posted.

If it helps, this question is related to one I posted a few weeks ago, for which YOU got the points:

http://www.experts-exchang

I tried to tweak it to get the sum of billing lines for each name:

SELECT distinct dictatorid, dictatorlastname,sumx

FROM (SELECT dictatorid,

dictatorlastname,

COUNT(DISTINCT dictatorlastname) OVER (PARTITION BY dictatorid) cnt,

SUM(BILLINGLINES) OVER (PARTITION BY dictatorlastname) sumx

FROM ichart.transactions

WHERE reportperiod = '201109')

WHERE cnt > 1

ORDER BY dictatorid ASC;

...and got back something like this:

DICTATORID DICTATORLASTNAME SUMX

---------- --------------------------

1893NOC Felder, K 6340

1893NOC Zavatsky, J 7985

30164NOC Gunn, S 4239

30164NOC Venturini, A 604

3682BRC Alleman, L 767

3682BRC Lindsey, S 21245

But when I double check the first dictatorid with an independent query:

Select Sum(billinglines) from ichart.transactions where reportperiod='201109' and dictatorid = '1893NOC';

I get 6669, NOT 14325 (6340 + 7985).

so since the simpler query returns different results, I have to assume there is something wrong with the more complicated one...

I hope this gives enough information. If not, I will keep trying.

(Its not always easy to provide enough information on the first or even subsequent tries.

I appreciate your patience.)

you need to partition/group by both ID and NAME

when you do something like this...

SUM(BILLINGLINES) OVER (PARTITION BY dictatorlastname)

it's reporting the sum of all lines by name, regardless of which id that name might be associated with.

So, if Felder, K is on ID 1, ID 2 and ID 3, you're summing all of those.

Is there a problem with the queries in http:#36957600 or http:#36957796 ?

they both return the requested results for the sample data provided.

DOH! 15 posts, wasting your time and mine (and HainKurt's), waiting most of the day for that simple solution because I did not ask the question properly in the first place....lesson learned....

In answer to your other question:

The expected output from the actual data is 52 rows (that is what I got when I did not try to sum anything).

Both of the queries you reference returned over 300 rows (even after adding the reportperiod criteria to the one where it was left out). So I will take the blame for that as well and assume there was something wrong with the sample data I provided as compared to the actual data.

At any rate, despite the length of the journey, I have the solution I came for, and that is what is most important. Once again, THANK YOU!

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

MS Access Sql Queto associate values from different rows | 3 | 35 | |

update set column values in oracle | 3 | 28 | |

Setting a NULL value to an empty string in SQL | 32 | 29 | |

SQL Select Help with xml tags in a text field | 9 | 21 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**18** Experts available now in Live!