Counting Rows in Query Groups

P

pachederm

Registered User.
Local time
Today, 16:03
Joined
Sep 14, 2011
Messages
13
Hi, I've been rattling my brain over this all day and was wondering if someone can help me out.

I have a query with about 7500 records that are grouped as follows and sorted by Mtr_Reading

ID Name Event_Start_Time _Hour, Mtr_Reading
2210 XYZ 7/15/2013 13:00 17 150
2210 XYZ 7/15/2013 13:00 14 143
2210 XYZ 7/15/2013 13:00 16 115
2210 XYZ 7/15/2013 13:00 15 110
2210 XYZ 7/15/2013 13:00 13 100
2210 XYZ 7/16/2013 12:00 12 100
2210 XYZ 7/16/2013 12:00 17 150
2210 XYZ 7/16/2013 12:00 14 147
2210 XYZ 7/16/2013 12:00 13 113
2210 XYZ 7/16/2013 12:00 18 110
2210 XYZ 7/16/2013 12:00 15 100

There are about 75 distinct sets of ID's and Names not shown here

The rows were sorted by the Mtr_Readings and we have to take the best four readings out of each group and average them. If a group has more than four rows then the top 4 are taken out and the balance is discarded. If the group has four rows or less all of the rows are taken.

I did try nested select statements and kept getting "invalid argument to function" exceptions.

Any help would be most appreciated.
 
Can you provide better sample data? You mention 75 distinct sets of ID's and Names but only provide 1 such example. Include more of them. Also include the table name and fields within those tables the data is from. Finally, and most importantly, based on your sample data provide what data you expect back from the final query.
 
Thanks for responding. I attached a screenshot of the query so that it can be explained pictorially as the file is very big.

Below in RED is the data we want which we will later average into a single number for ID/Name. We are basically taking the Top 4 records of each group.I have a lot of flexibility with how I can restructure the tables and did try adding an auto-number to the query which caused me to :banghead:

ID Name Event_Start_Time _Hour Mtr_Reading
2210 XYZ 7/15/2013 13:00 17 150
2210 XYZ 7/15/2013 13:00 14 143
2210 XYZ 7/15/2013 13:00 16 115
2210 XYZ 7/15/2013 13:00 15 110
2210 XYZ 7/15/2013 13:00 13 100
2210 XYZ 7/16/2013 12:00 12 100
2210 XYZ 7/16/2013 12:00 17 150
2210 XYZ 7/16/2013 12:00 14 147
2210 XYZ 7/16/2013 12:00 13 113
2210 XYZ 7/16/2013 12:00 18 110
2210 XYZ 7/16/2013 12:00 15 100
3330 ABC 7/15/2013 13:00 13 1200
3330 ABC 7/15/2013 13:00 15 1100
3330 ABC 7/15/2013 13:00 17 980
3330 ABC 7/15/2013 13:00 18 900
3330 ABC 7/15/2013 13:00 14 500
3330 ABC 7/16/2013 12:00 17 1100
3330 ABC 7/16/2013 12:00 15 1050
3330 ABC 7/16/2013 12:00 12 950
3330 ABC 7/16/2013 12:00 14 800
3330 ABC 7/16/2013 12:00 16 700
3330 ABC 7/16/2013 12:00 13 650




 

Attachments

  • Query Screenshot.pdf
    Query Screenshot.pdf
    70.6 KB · Views: 109
  • qBulkScreenshot.png
    qBulkScreenshot.png
    73.9 KB · Views: 88
  • qRID.jpg
    qRID.jpg
    100.7 KB · Views: 85
...Finally, and most importantly, based on your sample data provide what data you expect back from the final query.
Click to expand...

You provided more sample data, but you didn't give me the ultimate resulting data that should be output. Also, include the name of the datasource your sample data is from.
 
Thanks again.

I'm not sure if you downloaded the files in the last post which had some screen shots from the DB illustrating the issue.

I attached another file after exporting the query to Excel and manually tried to show what the resultant query would look like.

The data source is a query named qBulkProcess_Part2 which is generated by a cascade of about 3 other queries.

Referring to the text in this post, basically the resulting data set would be only the "red" rows and the "black" ones removed. This represents the top 4 rows of each group which I will later average to get the average reading for each group after the best 4 readings are filtered in.

ID Name Event_Start_Time _Hour Mtr_Reading
2210 XYZ 7/15/2013 13:00 17 150
2210 XYZ 7/15/2013 13:00 14 143
2210 XYZ 7/15/2013 13:00 16 115
2210 XYZ 7/15/2013 13:00 15 110
2210 XYZ 7/16/2013 12:00 12 100
2210 XYZ 7/16/2013 12:00 17 150
2210 XYZ 7/16/2013 12:00 14 147
2210 XYZ 7/16/2013 12:00 13 113
3330 ABC 7/15/2013 13:00 13 1200
3330 ABC 7/15/2013 13:00 15 1100
3330 ABC 7/15/2013 13:00 17 980
3330 ABC 7/15/2013 13:00 18 900
3330 ABC 7/16/2013 12:00 17 1100
3330 ABC 7/16/2013 12:00 15 1050
3330 ABC 7/16/2013 12:00 12 950
3330 ABC 7/16/2013 12:00 14 800
 

Attachments

  • ResultanQueryIllustration.png
    ResultanQueryIllustration.png
    90.4 KB · Views: 92
No more explanations. Give me data. Give me what data you are starting with, including the name of the data source and then what the exact resulting data should be.

If you want me to help you, your next post will contain just two sets of data: 1 with the starting data and one with the exact results that should be produced from that data starting data. Don't walk me through it, don't highlight stuff, don't get me to the 5 yard line then explain how I need to just go 5 more yards to get to the end zone. Starting data, exact ending data.
 
Hi Plog,

Thanks again for your response and assistance.

Below are two sets of data....the data source (qBulkProcess) and the resultant data set (qLimit4). I did add the field "index" which is an autonumber in hopes that it would help.


DATASOURCE (qBulkProcess)
index Resource ID Resource Name Event Start Date Net ACL Declared Value Avg CMD Curtailed kW _Hour 156588 2112360 Fordham LC Residence Hall 7/15/2013 13:00 493 492 1 492 14 156589 2112360 Fordham LC Residence Hall 7/15/2013 13:00 493 492 1 492 15 156591 2112360 Fordham LC Residence Hall 7/15/2013 13:00 493 492 1 492 17 156587 2112360 Fordham LC Residence Hall 7/15/2013 13:00 493 492 2 491 13 156590 2112360 Fordham LC Residence Hall 7/15/2013 13:00 493 492 2 491 16 156592 2112360 Fordham LC Residence Hall 7/15/2013 13:00 493 492 562 -69 18 156611 2112360 Fordham LC Residence Hall 7/16/2013 13:00 493 492 1 492 13 156612 2112360 Fordham LC Residence Hall 7/16/2013 13:00 493 492 1 492 14 156614 2112360 Fordham LC Residence Hall 7/16/2013 13:00 493 492 1 492 16 156615 2112360 Fordham LC Residence Hall 7/16/2013 13:00 493 492 1 492 17 156613 2112360 Fordham LC Residence Hall 7/16/2013 13:00 493 492 2 491 15 156616 2112360 Fordham LC Residence Hall 7/16/2013 13:00 493 492 540 -47 18 156636 2112360 Fordham LC Residence Hall 7/17/2013 13:00 493 492 1 492 14 156637 2112360 Fordham LC Residence Hall 7/17/2013 13:00 493 492 1 492 15 156638 2112360 Fordham LC Residence Hall 7/17/2013 13:00 493 492 1 492 16 156635 2112360 Fordham LC Residence Hall 7/17/2013 13:00 493 492 2 491 13 156639 2112360 Fordham LC Residence Hall 7/17/2013 13:00 493 492 2 491 17 156640 2112360 Fordham LC Residence Hall 7/17/2013 13:00 493 492 582 -89 18 156658 2112360 Fordham LC Residence Hall 7/18/2013 12:00 493 492 1 492 12 156661 2112360 Fordham LC Residence Hall 7/18/2013 12:00 493 492 1 492 15 156662 2112360 Fordham LC Residence Hall 7/18/2013 12:00 493 492 1 492 16 156659 2112360 Fordham LC Residence Hall 7/18/2013 12:00 493 492 2 491 13 156660 2112360 Fordham LC Residence Hall 7/18/2013 12:00 493 492 2 491 14 156663 2112360 Fordham LC Residence Hall 7/18/2013 12:00 493 492 2 491 17 156664 2112360 Fordham LC Residence Hall 7/18/2013 12:00 493 492 566 -73 18 156682 2112360 Fordham LC Residence Hall 7/19/2013 12:00 493 492 1 492 12 156683 2112360 Fordham LC Residence Hall 7/19/2013 12:00 493 492 1 492 13 156684 2112360 Fordham LC Residence Hall 7/19/2013 12:00 493 492 1 492 14 156686 2112360 Fordham LC Residence Hall 7/19/2013 12:00 493 492 1 492 16 156687 2112360 Fordham LC Residence Hall 7/19/2013 12:00 493 492 1 492 17 156685 2112360 Fordham LC Residence Hall 7/19/2013 12:00 493 492 2 491 15 156688 2112360 Fordham LC Residence Hall 7/19/2013 12:00 493 492 634 -141 18 157165 2112360 Fordham LC Residence Hall 8/8/2013 15:00 493 492 114 379 15 157166 2112360 Fordham LC Residence Hall 8/8/2013 15:00 493 492 233 260 16 162541 2113071 Fordham Lincoln Center 7/15/2013 13:00 1856 500 1480 376 17 162540 2113071 Fordham Lincoln Center 7/15/2013 13:00 1856 500 1501 355 16 162537 2113071 Fordham Lincoln Center 7/15/2013 13:00 1856 500 1511 345 13 162538 2113071 Fordham Lincoln Center 7/15/2013 13:00 1856 500 1520 336 14 162539 2113071 Fordham Lincoln Center 7/15/2013 13:00 1856 500 1570 286 15 162542 2113071 Fordham Lincoln Center 7/15/2013 13:00 1856 500 1751 105 18 162565 2113071 Fordham Lincoln Center 7/16/2013 13:00 1856 500 1438 418 17 162564 2113071 Fordham Lincoln Center 7/16/2013 13:00 1856 500 1495 361 16 162563 2113071 Fordham Lincoln Center 7/16/2013 13:00 1856 500 1526 330 15 162562 2113071 Fordham Lincoln Center 7/16/2013 13:00 1856 500 1533 323 14 162561 2113071 Fordham Lincoln Center 7/16/2013 13:00 1856 500 1543 313 13 162566 2113071 Fordham Lincoln Center 7/16/2013 13:00 1856 500 1722 134 18 162589 2113071 Fordham Lincoln Center 7/17/2013 13:00 1856 500 1413 443 17 162588 2113071 Fordham Lincoln Center 7/17/2013 13:00 1856 500 1498 358 16 162586 2113071 Fordham Lincoln Center 7/17/2013 13:00 1856 500 1508 348 14 162587 2113071 Fordham Lincoln Center 7/17/2013 13:00 1856 500 1542 314 15 162585 2113071 Fordham Lincoln Center 7/17/2013 13:00 1856 500 1562 294 13 162590 2113071 Fordham Lincoln Center 7/17/2013 13:00 1856 500 1733 123 18 162613 2113071 Fordham Lincoln Center 7/18/2013 12:00 1856 500 1452 404 17 162608 2113071 Fordham Lincoln Center 7/18/2013 12:00 1856 500 1488 368 12 162612 2113071 Fordham Lincoln Center 7/18/2013 12:00 1856 500 1488 368 16 162610 2113071 Fordham Lincoln Center 7/18/2013 12:00 1856 500 1515 341 14 162611 2113071 Fordham Lincoln Center 7/18/2013 12:00 1856 500 1517 339 15 162609 2113071 Fordham Lincoln Center 7/18/2013 12:00 1856 500 1526 330 13 162614 2113071 Fordham Lincoln Center 7/18/2013 12:00 1856 500 1700 156 18 162632 2113071 Fordham Lincoln Center 7/19/2013 12:00 1856 500 1283 573 12 162637 2113071 Fordham Lincoln Center 7/19/2013 12:00 1856 500 1337 519 17 162636 2113071 Fordham Lincoln Center 7/19/2013 12:00 1856 500 1387 469 16 162638 2113071 Fordham Lincoln Center 7/19/2013 12:00 1856 500 1390 466 18 162635 2113071 Fordham Lincoln Center 7/19/2013 12:00 1856 500 1395 461 15 162634 2113071 Fordham Lincoln Center 7/19/2013 12:00 1856 500 1397 459 14 162633 2113071 Fordham Lincoln Center 7/19/2013 12:00 1856 500 1421 435 13 163116 2113071 Fordham Lincoln Center 8/8/2013 15:00 1856 500 1821 35 16 163115 2113071 Fordham Lincoln Center 8/8/2013 15:00 1856 500 1851 5 15 152106 2112218 1 W 39 ST 7/15/2013 13:00 211 30 148 63 18 152105 2112218 1 W 39 ST 7/15/2013 13:00 211 30 153 58 17 152104 2112218 1 W 39 ST 7/15/2013 13:00 211 30 157 54 16 152103 2112218 1 W 39 ST 7/15/2013 13:00 211 30 164 47 15 152102 2112218 1 W 39 ST 7/15/2013 13:00 211 30 166 45 14 152101 2112218 1 W 39 ST 7/15/2013 13:00 211 30 167 44 13 152130 2112218 1 W 39 ST 7/16/2013 13:00 211 30 153 58 18 152129 2112218 1 W 39 ST 7/16/2013 13:00 211 30 156 55 17 152128 2112218 1 W 39 ST 7/16/2013 13:00 211 30 163 48 16 152127 2112218 1 W 39 ST 7/16/2013 13:00 211 30 171 40 15 152125 2112218 1 W 39 ST 7/16/2013 13:00 211 30 173 38 13 152126 2112218 1 W 39 ST 7/16/2013 13:00 211 30 175 36 14 152154 2112218 1 W 39 ST 7/17/2013 13:00 211 30 180 31 18 152152 2112218 1 W 39 ST 7/17/2013 13:00 211 30 186 25 16 152153 2112218 1 W 39 ST 7/17/2013 13:00 211 30 187 24 17 152149 2112218 1 W 39 ST 7/17/2013 13:00 211 30 193 18 13 152151 2112218 1 W 39 ST 7/17/2013 13:00 211 30 200 11 15 152150 2112218 1 W 39 ST 7/17/2013 13:00 211 30 202 9 14 152177 2112218 1 W 39 ST 7/18/2013 12:00 211 30 159 52 17 152178 2112218 1 W 39 ST 7/18/2013 12:00 211 30 159 52 18 152176 2112218 1 W 39 ST 7/18/2013 12:00 211 30 163 48 16 152175 2112218 1 W 39 ST 7/18/2013 12:00 211 30 165 46 15 152173 2112218 1 W 39 ST 7/18/2013 12:00 211 30 166 45 13 152174 2112218 1 W 39 ST 7/18/2013 12:00 211 30 166 45 14 152172 2112218 1 W 39 ST 7/18/2013 12:00 211 30 169 42 12 152202 2112218 1 W 39 ST 7/19/2013 12:00 211 30 152 59 18 152201 2112218 1 W 39 ST 7/19/2013 12:00 211 30 157 54 17 152200 2112218 1 W 39 ST 7/19/2013 12:00 211 30 163 48 16 152199 2112218 1 W 39 ST 7/19/2013 12:00 211 30 168 43 15 152197 2112218 1 W 39 ST 7/19/2013 12:00 211 30 169 42 13 152198 2112218 1 W 39 ST 7/19/2013 12:00 211 30 171 40 14 152196 2112218 1 W 39 ST 7/19/2013 12:00 211 30 178 33 12 152680 2112218 1 W 39 ST 8/8/2013 15:00 211 30 149 62 16 152679 2112218 1 W 39 ST 8/8/2013 15:00 211 30 154 57 15 1815 200076 100 6 Avnue Ent 7/15/2013 13:00 1434 250 1190 244 14 1819 200076 100 6 Avnue Ent 7/15/2013 13:00 1434 250 1207 227 18 1818 200076 100 6 Avnue Ent 7/15/2013 13:00 1434 250 1239 195 17 1817 200076 100 6 Avnue Ent 7/15/2013 13:00 1434 250 1245 189 16 1814 200076 100 6 Avnue Ent 7/15/2013 13:00 1434 250 1252 182 13 1816 200076 100 6 Avnue Ent 7/15/2013 13:00 1434 250 1259 175 15 1843 200076 100 6 Avnue Ent 7/16/2013 13:00 1434 250 1214 220 18 1838 200076 100 6 Avnue Ent 7/16/2013 13:00 1434 250 1274 160 13 1839 200076 100 6 Avnue Ent 7/16/2013 13:00 1434 250 1279 155 14 1840 200076 100 6 Avnue Ent 7/16/2013 13:00 1434 250 1286 148 15 1842 200076 100 6 Avnue Ent 7/16/2013 13:00 1434 250 1294 140 17 1841 200076 100 6 Avnue Ent 7/16/2013 13:00 1434 250 1295 139 16 1867 200076 100 6 Avnue Ent 7/17/2013 13:00 1434 250 1269 165 18 1862 200076 100 6 Avnue Ent 7/17/2013 13:00 1434 250 1281 153 13 1866 200076 100 6 Avnue Ent 7/17/2013 13:00 1434 250 1327 107 17 1863 200076 100 6 Avnue Ent 7/17/2013 13:00 1434 250 1336 98 14 1864 200076 100 6 Avnue Ent 7/17/2013 13:00 1434 250 1341 93 15 1865 200076 100 6 Avnue Ent 7/17/2013 13:00 1434 250 1364 70 16 1886 200076 100 6 Avnue Ent 7/18/2013 12:00 1434 250 1256 178 13 1891 200076 100 6 Avnue Ent 7/18/2013 12:00 1434 250 1286 148 18 1885 200076 100 6 Avnue Ent 7/18/2013 12:00 1434 250 1288 146 12 1888 200076 100 6 Avnue Ent 7/18/2013 12:00 1434 250 1288 146 15 1887 200076 100 6 Avnue Ent 7/18/2013 12:00 1434 250 1293 141 14 1890 200076 100 6 Avnue Ent 7/18/2013 12:00 1434 250 1295 139 17 1889 200076 100 6 Avnue Ent 7/18/2013 12:00 1434 250 1301 133 16 1915 200076 100 6 Avnue Ent 7/19/2013 12:00 1434 250 1290 144 18 1914 200076 100 6 Avnue Ent 7/19/2013 12:00 1434 250 1336 98 17 1911 200076 100 6 Avnue Ent 7/19/2013 12:00 1434 250 1364 70 14 1913 200076 100 6 Avnue Ent 7/19/2013 12:00 1434 250 1364 70 16 1909 200076 100 6 Avnue Ent 7/19/2013 12:00 1434 250 1365 69 12 1912 200076 100 6 Avnue Ent 7/19/2013 12:00 1434 250 1381 53 15 1910 200076 100 6 Avnue Ent 7/19/2013 12:00 1434 250 1391 43 13 2393 200076 100 6 Avnue Ent 8/8/2013 15:00 1434 250 1373 61 16 2392 200076 100 6 Avnue Ent 8/8/2013 15:00 1434 250 1446 -12 15
RESULT TABLE (qLimit4)

index Resource ID Resource Name Event Start Date Net ACL Declared Value Avg CMD Curtailed kW _Hour 156588 2112360 Fordham LC Residence Hall 7/15/13 13:00 493 492 1 492 14 156589 2112360 Fordham LC Residence Hall 7/15/13 13:00 493 492 1 492 15 156591 2112360 Fordham LC Residence Hall 7/15/13 13:00 493 492 1 492 17 156587 2112360 Fordham LC Residence Hall 7/15/13 13:00 493 492 2 491 13 156611 2112360 Fordham LC Residence Hall 7/16/13 13:00 493 492 1 492 13 156612 2112360 Fordham LC Residence Hall 7/16/13 13:00 493 492 1 492 14 156614 2112360 Fordham LC Residence Hall 7/16/13 13:00 493 492 1 492 16 156615 2112360 Fordham LC Residence Hall 7/16/13 13:00 493 492 1 492 17 156636 2112360 Fordham LC Residence Hall 7/17/13 13:00 493 492 1 492 14 156637 2112360 Fordham LC Residence Hall 7/17/13 13:00 493 492 1 492 15 156638 2112360 Fordham LC Residence Hall 7/17/13 13:00 493 492 1 492 16 156635 2112360 Fordham LC Residence Hall 7/17/13 13:00 493 492 2 491 13 156658 2112360 Fordham LC Residence Hall 7/18/13 12:00 493 492 1 492 12 156661 2112360 Fordham LC Residence Hall 7/18/13 12:00 493 492 1 492 15 156662 2112360 Fordham LC Residence Hall 7/18/13 12:00 493 492 1 492 16 156659 2112360 Fordham LC Residence Hall 7/18/13 12:00 493 492 2 491 13 156682 2112360 Fordham LC Residence Hall 7/19/13 12:00 493 492 1 492 12 156683 2112360 Fordham LC Residence Hall 7/19/13 12:00 493 492 1 492 13 156684 2112360 Fordham LC Residence Hall 7/19/13 12:00 493 492 1 492 14 156686 2112360 Fordham LC Residence Hall 7/19/13 12:00 493 492 1 492 16 157165 2112360 Fordham LC Residence Hall 8/8/13 15:00 493 492 114 379 15 157166 2112360 Fordham LC Residence Hall 8/8/13 15:00 493 492 233 260 16 162541 2113071 Fordham Lincoln Center 7/15/13 13:00 1856 500 1480 376 17 162540 2113071 Fordham Lincoln Center 7/15/13 13:00 1856 500 1501 355 16 162537 2113071 Fordham Lincoln Center 7/15/13 13:00 1856 500 1511 345 13 162538 2113071 Fordham Lincoln Center 7/15/13 13:00 1856 500 1520 336 14 162565 2113071 Fordham Lincoln Center 7/16/13 13:00 1856 500 1438 418 17 162564 2113071 Fordham Lincoln Center 7/16/13 13:00 1856 500 1495 361 16 162563 2113071 Fordham Lincoln Center 7/16/13 13:00 1856 500 1526 330 15 162562 2113071 Fordham Lincoln Center 7/16/13 13:00 1856 500 1533 323 14 162589 2113071 Fordham Lincoln Center 7/17/13 13:00 1856 500 1413 443 17 162588 2113071 Fordham Lincoln Center 7/17/13 13:00 1856 500 1498 358 16 162586 2113071 Fordham Lincoln Center 7/17/13 13:00 1856 500 1508 348 14 162587 2113071 Fordham Lincoln Center 7/17/13 13:00 1856 500 1542 314 15 162613 2113071 Fordham Lincoln Center 7/18/13 12:00 1856 500 1452 404 17 162608 2113071 Fordham Lincoln Center 7/18/13 12:00 1856 500 1488 368 12 162612 2113071 Fordham Lincoln Center 7/18/13 12:00 1856 500 1488 368 16 162610 2113071 Fordham Lincoln Center 7/18/13 12:00 1856 500 1515 341 14 162632 2113071 Fordham Lincoln Center 7/19/13 12:00 1856 500 1283 573 12 162637 2113071 Fordham Lincoln Center 7/19/13 12:00 1856 500 1337 519 17 162636 2113071 Fordham Lincoln Center 7/19/13 12:00 1856 500 1387 469 16 162638 2113071 Fordham Lincoln Center 7/19/13 12:00 1856 500 1390 466 18 163116 2113071 Fordham Lincoln Center 8/8/13 15:00 1856 500 1821 35 16 163115 2113071 Fordham Lincoln Center 8/8/13 15:00 1856 500 1851 5 15 152106 2112218 1 W 39 ST 7/15/13 13:00 211 30 148 63 18 152105 2112218 1 W 39 ST 7/15/13 13:00 211 30 153 58 17 152104 2112218 1 W 39 ST 7/15/13 13:00 211 30 157 54 16 152103 2112218 1 W 39 ST 7/15/13 13:00 211 30 164 47 15 152130 2112218 1 W 39 ST 7/16/13 13:00 211 30 153 58 18 152129 2112218 1 W 39 ST 7/16/13 13:00 211 30 156 55 17 152128 2112218 1 W 39 ST 7/16/13 13:00 211 30 163 48 16 152127 2112218 1 W 39 ST 7/16/13 13:00 211 30 171 40 15 152154 2112218 1 W 39 ST 7/17/13 13:00 211 30 180 31 18 152152 2112218 1 W 39 ST 7/17/13 13:00 211 30 186 25 16 152153 2112218 1 W 39 ST 7/17/13 13:00 211 30 187 24 17 152149 2112218 1 W 39 ST 7/17/13 13:00 211 30 193 18 13 152177 2112218 1 W 39 ST 7/18/13 12:00 211 30 159 52 17 152178 2112218 1 W 39 ST 7/18/13 12:00 211 30 159 52 18 152176 2112218 1 W 39 ST 7/18/13 12:00 211 30 163 48 16 152175 2112218 1 W 39 ST 7/18/13 12:00 211 30 165 46 15 152202 2112218 1 W 39 ST 7/19/13 12:00 211 30 152 59 18 152201 2112218 1 W 39 ST 7/19/13 12:00 211 30 157 54 17 152200 2112218 1 W 39 ST 7/19/13 12:00 211 30 163 48 16 152199 2112218 1 W 39 ST 7/19/13 12:00 211 30 168 43 15 152680 2112218 1 W 39 ST 8/8/13 15:00 211 30 149 62 16 152679 2112218 1 W 39 ST 8/8/13 15:00 211 30 154 57 15 1815 200076 100 6 Avnue Ent 7/15/13 13:00 1434 250 1190 244 14 1819 200076 100 6 Avnue Ent 7/15/13 13:00 1434 250 1207 227 18 1818 200076 100 6 Avnue Ent 7/15/13 13:00 1434 250 1239 195 17 1817 200076 100 6 Avnue Ent 7/15/13 13:00 1434 250 1245 189 16 1843 200076 100 6 Avnue Ent 7/16/13 13:00 1434 250 1214 220 18 1838 200076 100 6 Avnue Ent 7/16/13 13:00 1434 250 1274 160 13 1839 200076 100 6 Avnue Ent 7/16/13 13:00 1434 250 1279 155 14 1840 200076 100 6 Avnue Ent 7/16/13 13:00 1434 250 1286 148 15 1867 200076 100 6 Avnue Ent 7/17/13 13:00 1434 250 1269 165 18 1862 200076 100 6 Avnue Ent 7/17/13 13:00 1434 250 1281 153 13 1866 200076 100 6 Avnue Ent 7/17/13 13:00 1434 250 1327 107 17 1863 200076 100 6 Avnue Ent 7/17/13 13:00 1434 250 1336 98 14 1886 200076 100 6 Avnue Ent 7/18/13 12:00 1434 250 1256 178 13 1891 200076 100 6 Avnue Ent 7/18/13 12:00 1434 250 1286 148 18 1885 200076 100 6 Avnue Ent 7/18/13 12:00 1434 250 1288 146 12 1888 200076 100 6 Avnue Ent 7/18/13 12:00 1434 250 1288 146 15 1915 200076 100 6 Avnue Ent 7/19/13 12:00 1434 250 1290 144 18 1914 200076 100 6 Avnue Ent 7/19/13 12:00 1434 250 1336 98 17 1911 200076 100 6 Avnue Ent 7/19/13 12:00 1434 250 1364 70 14 1913 200076 100 6 Avnue Ent 7/19/13 12:00 1434 250 1364 70 16 2393 200076 100 6 Avnue Ent 8/8/13 15:00 1434 250 1373 61 16 2392 200076 100 6 Avnue Ent 8/8/13 15:00 1434 250 1446 -12 15
 
sorry attached please find the actual excel spreadsheet with the data source and resultant table.

the last post looked a lot nicer on my screen before I hit the submit button.
 

Attachments

What orders your data? Look at [ResourceID]=2112360, [Event Start Date]=7/15/13 13:00 in your DataSource. You have 6 records for those values. How are you deciding which 4 to include in the final table?

You've stated 'the top 4', but that implies you have an order to your data. What field orders your data?
 
Hi,

The data is ordered by "Resource Name" (ascending), "Event Start Date" (ascending) and "Curtailed kW" (descending). The max value for "Curtailed kW" is always at the top of each group. If there are more than 4 rows (i.e. 6), then I can only take the best 4 out of six (i.e. the top 4). If there are 4 or less rows, then I take the whole group.

Does this explain that?

Thanks,
Leo
 
Yes and no. I don't think you are correct. I don't believe [Event Start Date] is part of the ordering of data, but part of the grouping of data.

Tell me if this is correct:

1. Data is grouped by [ResourceID] and [Event Start Date]

2. After grouping the data is ordered by [Curtailed KW] in descending order.
 
I'm looking over your initial post and you mention averages and balances and that's not what your result data has in it. It's a subset of your starting data.

If I am able to create a query to take your starting data and generate your ending data you provided does that actually give you the final results you want?
 
Hi Plog,

Yes, Data is grouped by [Resource ID] and [Event Start Date]
Afterwards the data in each group is ordered by [Curtailed kW] in descending order.

Getting a subset of the starting data via SQL is the most complicated part. In the end, I will need a data set that is the average of the 4 [Curtailed kW] values in each group.

So it would look something like

[Resource ID], [Resource Name], [Event Start Date], [Average of Curtailed kW]

Thanks,
Leo




QueryDesign.png
 
So now we are back to you providing me with sample starting data and resulting data.

Let's go again. Provide me with sample starting data and then what the exact data is that should be produced from the sample starting data.
 
Hi Plog,

I'm not sure how I can make it any clearer. The excel spreadsheet in my previous posts has a tab labeled "result table" - I have attached it again.

I am just looking for a subset of the original data source query, which again is the first 4 rows of each group.

The resultant data set will have the following fields

[Resource ID], [Resource Name], [Event Start Date], [_Hour], [Curtailed kW]

Everything else I need after that is irrelevant for this request.

Thanks,
Leo
 

Attachments

You can make it clearer by telling me the exact resulting data you want. It doesn't sound like you really want to identify the 'top 4' records in a grouping, but the average of their values. Show me what you exactly want the result to be.
 
You must log in or register to reply here.

Similar threads

K
Group tables
Replies
14
Views
727
jdelano
J
M
Crosstab report with calculated label to show each day of month as label and working hours depending on employee shifts
Replies
2
Views
1,081
Gasman
Gasman
MajP
Solved Aggregate Query Returns Different Values When a Field is Included not in Group By
2
Replies
27
Views
2,388
MajP
MajP
T
Solved Convert amount to words
Replies
18
Views
1,560
Tiprof
T
K
Solved Hi I want to VBA Code for Currency to Words in Indian format but One Hundred Thousand instead of Lakh
Replies
4
Views
1,665
DickyP
DickyP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom