Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL REQUEST PROBLEM

Posted on 2012-04-13
Medium Priority
272 Views
hi,

I've this sql request :

SELECT CODE_FAMILLE,SUM(MONTANT) AS TOTAL,ANNEE
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY ANNEE,CODE_FAMILLE
ORDER BY CODE_FAMILLE

And the result is

BAND      6.75      N-1
CARR      74699.51      N
CARR      49860.48      N-1
COLL      78562.34      N
COLL      59139.02      N-1
DIV      233.04      N
DIV      -666.03      N-1
ENTR      72.66      N-1
EVAC      240.30      N
NATT      220.00      N
NATT      5550.00      N-1
OUT      1302.95      N
OUT      20.64      N-1
PRO      776.37      N
PRO      394.86      N-1
TRA      228.69      N
TRA      11.02      N-1

I would like to have this result

CODE_FAMILLE     N               N-1
BAND                      6.75          NULL
CARR                       74699.51   49860.48
COLL                        78562.34   59139.02

etc

How to do that
I've a SQL SERVER 2005 Express

0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 3
• 2
• +2

Expert Comment

ID: 37841728
Try

SELECT CODE_FAMILLE,SUM(MONTANT) AS TOTAL,ANNEE
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE
ORDER BY ANNEE

or

SELECT CODE_FAMILLE,SUM(MONTANT) AS TOTAL,ANNEE
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE,ANNEE
ORDER BY CODE_FAMILLE
0

LVL 6

Author Comment

ID: 37841751
Not functionnaly
0

LVL 93

Expert Comment

ID: 37842038
SELECT t1.CODE_FAMILLE, SUM(t1.MONTANT) AS N, SUM(t2.MONTANT) AS [N-1]
FROM import_stat_client_produit t1 LEFT JOIN
import_stat_client_produit t2 ON t1.CODE_FAMILLE = t2.CODE_FAMILLE
WHERE t1.ANNEE = 'N' AND t2.ANNEE = 'N-1'
GROUP BY t1.CODE_FAMILLE
0

LVL 6

Author Comment

ID: 37842143
Almost

CODE_FAMILLE      N                    N-1
CARR      702175394.00      336451921.38
COLL      440840789.52      60277033.44
DIV      53133.12                      90549.44
EVAC      79539.30                  114376.65
NATT      59400.00                      56288.79
OUT      1986998.75      350592.21
PRO      1473550.26      452513.90
TRA      15550.92                      15316.40

But the sum is all the CODE_CLIENT NOT CODE_CLIENT = 235
0

LVL 5

Expert Comment

ID: 37842202
TRY:

SELECT CODE_FAMILLE,
CASE ANNEE
WHEN 'N' THEN SUM(MONTANT)
ELSE 0
END,
CASE ANNEE
WHEN 'N-1' THEN SUM(MONTANT)
END
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE
ORDER BY CODE_FAMILLE
0

LVL 6

Author Comment

ID: 37842218
Not good

the result is
BAND      0.00      6.75
CARR      74699.51      NULL
CARR      0.00      49860.48
COLL      78665.38      NULL
COLL      0.00      59139.02
DIV      233.04      NULL
DIV      0.00      -666.03
ENTR      0.00      72.66
EVAC      240.30      NULL
NATT      220.00      NULL
NATT      0.00      5550.00
OUT      1302.95      NULL
OUT      0.00      20.64
PRO      776.37      NULL
PRO      0.00      394.86
TRA      228.69      NULL
TRA      0.00      11.02
0

LVL 5

Expert Comment

ID: 37842226
Then try "matthewspatrick" query with a slight amendment:

SELECT t1.CODE_FAMILLE, SUM(t1.MONTANT) AS N, SUM(t2.MONTANT) AS [N-1]
FROM import_stat_client_produit t1 LEFT JOIN
import_stat_client_produit t2 ON t1.CODE_FAMILLE = t2.CODE_FAMILLE
WHERE t1.ANNEE = 'N' AND t2.ANNEE = 'N-1'
AND t1.CODE_CLIENT = 235
AND t2.CODE_CLIENT = t1.CODE_CLIENT
GROUP BY t1.CODE_FAMILLE
0

LVL 6

Author Comment

ID: 37842283
CARR      9337438.75      7927816.32
COLL      8023868.76      6150458.08
DIV      1864.32      -1332.06
NATT      440.00      5550.00
OUT      11726.55      185.76
PRO      7763.70      1974.30
TRA      457.38      44.08

not good sum
0

LVL 8

Assisted Solution

tomvergote earned 1200 total points
ID: 37842372
it's not clear to me what the actual expected output is...
but if it's the nulls that are wrong in gman84's suggestion try this
SELECT CODE_FAMILLE,
CASE ANNEE
WHEN 'N' THEN SUM(MONTANT)
END,
CASE ANNEE
WHEN 'N-1' THEN SUM(MONTANT)
END
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE
ORDER BY CODE_FAMILLE
0

LVL 93

Expert Comment

ID: 37842379
Trying again:

``````SELECT t1.CODE_FAMILLE, SUM(t1.MONTANT) AS N, SUM(t2.MONTANT) AS [N-1]
FROM import_stat_client_produit t1 LEFT JOIN
import_stat_client_produit t2 ON t1.CODE_FAMILLE = t2.CODE_FAMILLE
WHERE t1.CODE_CLIENT = 235 AND t1.ANNEE = 'N' AND (t2.ANNEE = 'N-1' OR t2.ANNEE IS NULL) AND
(t2.CODE_CLIENT = 235 OR CODE_CLIENT IS NULL)
GROUP BY t1.CODE_FAMILLE
``````
0

LVL 6

Accepted Solution

Manuel Marienne-DuchĂȘne earned 0 total points
ID: 37842470
I Found

SELECT CODE_FAMILLE,
SUM(CASE WHEN CODE_CLIENT = 235 AND ANNEE = 'N'THEN MONTANT ELSE 0 END) AS N,
SUM(CASE WHEN CODE_CLIENT = 235 AND ANNEE = 'N-1' THEN MONTANT ELSE 0 END) AS [N-1]
FROM import_stat_client_produit
WHERE CODE_CLIENT = 235
GROUP BY CODE_FAMILLE
ORDER BY CODE_FAMILLE

And the result is
CODE_FA. N                N-1
BAND      0.00      6.75
CARR      74699.51      49860.48
COLL      78665.38      59139.02
DIV      233.04      -666.03
ENTR      0.00      72.66
EVAC      240.30      0.00
NATT      220.00      5550.00
OUT      1302.95      20.64
PRO      776.37      394.86
TRA      228.69      11.02
0

LVL 5

Expert Comment

ID: 37842483
Doh! Yes of course
0

LVL 6

Author Comment

ID: 37842492
thank you all for your help
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from BackupâŠ
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
###### Suggested Courses
Course of the Month11 days, 13 hours left to enroll