grouping

Question

Post reply

grouping

lcarrethers

SSCommitted

Points: 1917
More actions
May 10, 2010 at 9:01 am

#224398

0288 INV0000083073 217.08000
0288X2 RESALE INV0000083063 229.00000
0511 INV0000083071 336.28000
0588 INV0000083064 303.50000
1421X3 REL/ED INV0000083065 154.50000
2619X4 GOV INV0000083066 191.75000
6071X7 AGRIC INV0000083068 109.80000
6088X6 MFG INV0000083069 163.44000
6588X8 INTERST INV0000083070 263.27000

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

vijay.s SSCrazy Points: 2161 More actions · Answer 1

vijay.s

SSCrazy

Points: 2161

May 10, 2010 at 9:05 am

#1165049

What is this? What is your query and hint????

lcarrethers SSCommitted Points: 1917 More actions · Answer 2

A B C D

0288 OK2 ALFALFA 02INV0000083073 217.08000

0588 OK2 BECKHAM 05INV0000083064 303.50000

0511 OK2 BECKHAM 05INV0000083071 336.28000

0288X2 RESALE OK2XALFALFA 02INV0000083063 229.00000

1421X3 REL/ED OK2XCLEVELAND14INV0000083065 154.50000

2619X4 GOV OK2XGRADY 26INV0000083066 191.75000

6071X7 AGRIC OK2XPAYNE 60INV0000083068 109.80000

6088X6 MFG OK2XPAYNE 60INV0000083069 163.44000

6588X8 INTERST OK2XROGER MIL65INV0000083070 263.27000

This is just data in a table.

Here is the query i have to sum my totals:

SELECT substring(TAXDTLID,5,15)AS TAXDTLID, Sum(GROSSAMNT) AS GROSSAMNT

--INTO ##COUNTYSUB

FROM COUNTY

GROUP BY substring(TAXDTLID,5,15)

ALFALFA 02446.08000

BECKHAM 05639.78000

CLEVELAND14154.50000

GRADY 26191.75000

PAYNE 60273.24000

ROGER MIL65263.27000

This is what i want but i also want to include Column A with from the 1st set of results, but when i add that column in there with the group all numbers are off.

Lowell SSC Guru Points: 323482 More actions · Answer 3

since you are new, I'm helping out and providing the work table and the data in a consumable format;

that way anyone can test a query against it and confirm results.

In the future, if you are able to do this, you will be surprised how many people jump at the opportunity to help:

CREATE TABLE BESTGUESS(

VARID varchar(30),

NOTE VARCHAR(30),

SOMEINVOICE VARCHAR(30),

SOMEDECIMAL DECIMAL (19,5) )

INSERT INTO BESTGUESS

SELECT '0288',NULL,'INV0000083073',217.08000 UNION ALL

SELECT '0288X2','RESALE','INV0000083063',229.00000 UNION ALL

SELECT '0511',NULL,'INV0000083071',336.28000 UNION ALL

SELECT '0588',NULL,'INV0000083064',303.50000 UNION ALL

SELECT '1421X3','REL/ED','INV0000083065',154.50000 UNION ALL

SELECT '2619X4','GOV','INV0000083066',191.75000 UNION ALL

SELECT '6071X7','AGRIC','INV0000083068',109.80000 UNION ALL

SELECT '6088X6','MFG','INV0000083069',163.44000 UNION ALL

SELECT '6588X8','INTERST','INV0000083070',263.27000

SELECT * FROM BESTGUESS

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vijay.s SSCrazy Points: 2161 More actions · Answer 4

Can you add semple script for the same

Like create a temp table or table variable and insert your sample data into table and also always use sql quote for the same.

Lowell SSC Guru Points: 323482 More actions · Answer 5

between your two posts, the data changed; more columns, other null fields, etc.

here is the second pass; this xemplifies why posting the table and data works so well...it avoids confusion:

CREATE TABLE BESTGUESS2(VARID varchar(30),

NOTE VARCHAR(30),

SOMEPLACE VARCHAR(30),

SOMECODE VARCHAR(30),

SOMEINVOICE VARCHAR(30),

SOMEDECIMAL DECIMAL (19,5) )

INSERT INTO BESTGUESS2

SELECT '0288','OK2','ALFALFA','02','INV0000083073',217.08000 UNION ALL

SELECT '0588','OK2','BECKHAM','05','INV0000083064',303.50000 UNION ALL

SELECT '0511','OK2','BECKHAM','05','INV0000083071',336.28000 UNION ALL

SELECT '0288X2','RESALE','OK2XALFALFA','02','INV0000083063',229.00000 UNION ALL

SELECT '1421X3','REL/ED','OK2XCLEVELAND14',NULL,'INV0000083065',154.50000 UNION ALL

SELECT '2619X4','GOV','OK2XGRADY','26','INV0000083066',191.75000 UNION ALL

SELECT '6071X7','AGRIC','OK2XPAYNE','60','INV0000083068',109.80000 UNION ALL

SELECT '6088X6','MFG','OK2XPAYNE','60','INV0000083069',163.44000 UNION ALL

SELECT '6588X8','INTERST','OK2XROGER','MIL65','INV0000083070',263.27000

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lcarrethers SSCommitted Points: 1917 More actions · Answer 6

lcarrethers

SSCommitted

Points: 1917

May 10, 2010 at 9:26 am

#1165066

i'm getting the sample code together for you now.

lcarrethers SSCommitted Points: 1917 More actions · Answer 7

CREATE TABLE BESTGUESS2(VARID varchar(15),

SOMEPLACE VARCHAR(15),

SOMEINVOICE VARCHAR(30),

SOMEDECIMAL DECIMAL (19,5) )

INSERT INTO BESTGUESS2

SELECT '0288','OK2 ALFALFA 02','INV0000083073',217.08000 UNION ALL

SELECT '0588','OK2 BECKHAM 05','INV0000083064',303.50000 UNION ALL

SELECT '0511','OK2 BECKHAM 05','INV0000083071',336.28000 UNION ALL

SELECT '0288X2 RESALE', 'OK2XALFALFA 02','INV0000083063',229.00000 UNION ALL

SELECT '1421X3 REL/ED', 'OK2XCLEVELAND14','INV0000083065',154.50000 UNION ALL

SELECT '2619X4 GOV', 'OK2XGRADY 26','INV0000083066',191.75000 UNION ALL

SELECT '6071X7 AGRIC', 'OK2XPAYNE 60','INV0000083068',109.80000 UNION ALL

SELECT '6088X6 MFG', 'OK2XPAYNE 60','INV0000083069',163.44000 UNION ALL

SELECT '6588X8 INTERST', 'OK2XROGER MIL65','INV0000083070',263.27000

SELECT * FROM BESTGUESS2

SELECT substring(someplace,5,15)AS someplace, Sum(somedecimal) AS somedecimal

FROM BESTGUESS2

GROUP BY substring(someplace,5,15)

This gives my my totals i want but like i said i want the varid field added and some way i will need toget one of one of the ones that was added.

I'm new and i hope this makes sense

Lowell SSC Guru Points: 323482 More actions · Answer 8

ok i think this is doing what you want;

i'm getting the original group as a sub select, and then rejoining it to the original table.

i think there was an error in your original substring, since GRADY/PAYNR/ROGER was being eliminated.

here's my results:

VARID SOMEPLACE SOMEDECIMAL

0288X2 ALFALFA 446.08000

0288 ALFALFA 446.08000

0588 BECKHAM 639.78000

0511 BECKHAM 639.78000

1421X3 CLEVELAND14 154.50000

2619X4 GRADY 191.75000

6071X7 PAYNE 273.24000

6088X6 PAYNE 273.24000

6588X8 ROGER 263.27000

and heres the code to test: