query special for totals

Question

Post reply

query special for totals

antonio arciga

SSC-Addicted

Points: 463
More actions
April 17, 2009 at 9:16 am

#203720

Example table one
citycustcodepayment
------------------------------------
10259925396.66
132088253164.17
152181253128.34
152181253128.34
112706255256.68
102566255424
102756255128.34
102566254106
92630253378.34
9263025275.66
92630251103.86
82563253188.94
8252825392.26
92629253109.69
82530253128.34
102581253128.34
52221253128.34
102752253178.34
92632253278.34
142718253128.34
102604253328.49
10260425265.69
10260425178.72
102074253204.01
92626253327.83
9262625265.56
9262625178.42
52690253221.18
82741253119.83
52233253128.34
92059253182.96
52231253112.37
82537253128.34
32129253178.34
102371253128.34
92668253127.83
52237253128.34
92108253137.28
12279253149.45
32509253278.34
5268325372.28
92636253128.34
52686253128.34
92644253218.65
102206253168.88
102274253128.34
102781253122.92
132698253178.54
142720253128.34
82337253128.34
I need build a query to get this results
citysum251sum252sum253sum254and255total
-------------------------------------------------------------
100149.450149.45
300456.680456.68
500919.190919.19
800786.050786.05
9182.28141.221889.2602212.76
1078.7265.691484.32658.342287.09
11000256.68256.68
1300342.710342.71
1400256.680256.68
1500256.680256.68
--------------------------------------------------------
sum261.00206.916541.02915.027923.97
Help me, please

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Is this for SQL 2000, 2005 or 2008? If 2005 or higher, look up Pivot in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

Also please post some description about your output columns like ex: 'Sum251'...

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

antonio arciga SSC-Addicted Points: 463 More actions · Answer 3

--===== Create the test table with

CREATE TABLE [dbo].[transactions](

[city] [int] NULL,

[customer] [int] NULL,

[int] NULL,

[payment] [real] NULL

) ON [PRIMARY]

INSERT INTO transaction (city, customer, code, payment)

SELECT '10','2599','253','96.66' UNION ALL

SELECT '13','2088','253','164.17' UNION ALL

SELECT '15','2181','253','128.34' UNION ALL

SELECT '11','2706','255','256.68' UNION ALL

SELECT '10','2566','255','424' UNION ALL

SELECT '10','2756','255','128.34' UNION ALL

SELECT '10','2566','254','106' UNION ALL

SELECT '9','2630','253','378.34' UNION ALL

SELECT '9','2630','252','75.66' UNION ALL

SELECT '9','2630','251','103.86' UNION ALL

SELECT '8','2563','253','188.94' UNION ALL

SELECT '8','2528','253','92.26' UNION ALL

SELECT '9','2629','253','109.69' UNION ALL

SELECT '8','2530','253','128.34' UNION ALL

SELECT '10','2581','253','128.34' UNION ALL

SELECT '5','2221','253','128.34' UNION ALL

SELECT '10','2752','253','178.34' UNION ALL

SELECT '9','2632','253','278.34' UNION ALL

SELECT '14','2718','253','128.34' UNION ALL

SELECT '10','2604','253','328.49' UNION ALL

SELECT '10','2604','252','65.69' UNION ALL

SELECT '10','2604','251','78.72' UNION ALL

SELECT '10','2074','253','204.01' UNION ALL

SELECT '9','2626','253','327.83' UNION ALL

SELECT '9','2626','252','65.56' UNION ALL

SELECT '9','2626','251','78.42' UNION ALL

SELECT '5','2690','253','221.18' UNION ALL

SELECT '8','2741','253','119.83' UNION ALL

SELECT '5','2233','253','128.34' UNION ALL

SELECT '9','2059','253','182.96' UNION ALL

SELECT '5','2231','253','112.37' UNION ALL

SELECT '8','2537','253','128.34' UNION ALL

SELECT '3','2129','253','178.34' UNION ALL

SELECT '10','2371','253','128.34' UNION ALL

SELECT '9','2668','253','127.83' UNION ALL

SELECT '5','2237','253','128.34' UNION ALL

SELECT '9','2108','253','137.28' UNION ALL

SELECT '1','2279','253','149.45' UNION ALL

SELECT '3','2509','253','278.34' UNION ALL

SELECT '5','2683','253','72.28' UNION ALL

SELECT '9','2636','253','128.34' UNION ALL

SELECT '5','2686','253','128.34' UNION ALL

SELECT '9','2644','253','218.65' UNION ALL

SELECT '10','2206','253','168.88' UNION ALL

SELECT '10','2274','253','128.34' UNION ALL

SELECT '10','2781','253','122.92' UNION ALL

SELECT '13','2698','253','178.54' UNION ALL

SELECT '14','2720','253','128.34' UNION ALL

SELECT '8','2337','253','128.34' UNION ALL

the column name 'code' identify a kind of payment

251 dectp - direct electronic transfer payment

252 cp - cash payment

253 cc - credit card payment

254 pep - paypal ebay payment

255 ot - other

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

individual querys for each code payment

select city,sum(round(payment,2)) as dectp

from transactions

where code =251

group by city

order by city

select city,sum(round(payment,2)) as cp

from transactions

where code =252

group by city

order by city

select city,sum(round(payment,2)) as cc

from transactions

where code =253

group by city

order by city

select city,sum(round(payment,2)) as 'pep+ot'

from transactions

where code between 254 and 255

group by city

order by city

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

I need build one query to get all together

A query like this

select city,

'dectp' = case when code=251 then sum(round(payment,2)) end,

'cp' = case when code=252 then sum(round(payment,2)) end,

'cc' = case when code=253 then sum(round(payment,2)) end,

'pep+ot' = case when code between 254 and 255 then sum(round(payment,2)) end

from transactions

group by city

order by city

I know it does not work, it's only some idea.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

SQL 2000, SQL 2005 or SQL 2008?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

antonio arciga SSC-Addicted Points: 463 More actions · Answer 5

antonio arciga

SSC-Addicted

Points: 463

April 18, 2009 at 10:57 am

#980223

I use SQL 2005...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Check out the PIVOT keyword in Books Online. It should do what you want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

select city,

sum(case when code=251 then round(payment,2) else 0 end) as dectp,

sum(case when code=252 then round(payment,2) else 0 end) as cp,

sum(case when code=253 then round(payment,2) else 0 end) as cc,

sum(case when code between 254 and 255 then round(payment,2) else 0 end) as 'pep+ot'

from transactions

group by city

order by city

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

antonio arciga SSC-Addicted Points: 463 More actions · Answer 8

antonio arciga

SSC-Addicted

Points: 463

April 20, 2009 at 7:44 am

#980705

Mark, thanks....

It works...