query special for totals
-
Example table one
city cust code payment
------------------------------------
10 2599 253 96.66
13 2088 253 164.17
15 2181 253 128.34
15 2181 253 128.34
11 2706 255 256.68
10 2566 255 424
10 2756 255 128.34
10 2566 254 106
9 2630 253 378.34
9 2630 252 75.66
9 2630 251 103.86
8 2563 253 188.94
8 2528 253 92.26
9 2629 253 109.69
8 2530 253 128.34
10 2581 253 128.34
5 2221 253 128.34
10 2752 253 178.34
9 2632 253 278.34
14 2718 253 128.34
10 2604 253 328.49
10 2604 252 65.69
10 2604 251 78.72
10 2074 253 204.01
9 2626 253 327.83
9 2626 252 65.56
9 2626 251 78.42
5 2690 253 221.18
8 2741 253 119.83
5 2233 253 128.34
9 2059 253 182.96
5 2231 253 112.37
8 2537 253 128.34
3 2129 253 178.34
10 2371 253 128.34
9 2668 253 127.83
5 2237 253 128.34
9 2108 253 137.28
1 2279 253 149.45
3 2509 253 278.34
5 2683 253 72.28
9 2636 253 128.34
5 2686 253 128.34
9 2644 253 218.65
10 2206 253 168.88
10 2274 253 128.34
10 2781 253 122.92
13 2698 253 178.54
14 2720 253 128.34
8 2337 253 128.34
I need build a query to get this results
city sum251 sum252 sum253 sum254and255 total
-------------------------------------------------------------
1 0 0 149.45 0 149.45
3 0 0 456.68 0 456.68
5 0 0 919.19 0 919.19
8 0 0 786.05 0 786.05
9 182.28 141.22 1889.26 0 2212.76
10 78.72 65.69 1484.32 658.34 2287.09
11 0 0 0 256.68 256.68
13 0 0 342.71 0 342.71
14 0 0 256.68 0 256.68
15 0 0 256.68 0 256.68
--------------------------------------------------------
sum 261.00 206.91 6541.02 915.02 7923.97
Help me, please
-
Please post table definitions. Read this to see the best way to post this to get quick responses.
http://qa.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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Also please post some description about your output columns like ex: 'Sum251'...
Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers
-
--===== 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 '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.
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I use SQL 2005...
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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
-
Mark, thanks....
It works...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply