Solved

SQL REQUEST PROBLEM

Posted on 2012-04-13
13
258 Views
Last Modified: 2012-04-18
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

Thanks for your help
0
Comment
  • 6
  • 3
  • 2
  • +2
13 Comments
 

Expert Comment

by:MHITSupport
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

by:Manuel Marienne-Duchêne
ID: 37841751
Not functionnaly
0
 
LVL 92

Expert Comment

by:Patrick Matthews
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

by:Manuel Marienne-Duchêne
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

by:gman84
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

by:Manuel Marienne-Duchêne
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 5

Expert Comment

by:gman84
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

by:Manuel Marienne-Duchêne
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

by:tomvergote
tomvergote earned 400 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 92

Expert Comment

by:Patrick Matthews
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

Open in new window

0
 
LVL 6

Accepted Solution

by:
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

by:gman84
ID: 37842483
Doh! Yes of course
0
 
LVL 6

Author Comment

by:Manuel Marienne-Duchêne
ID: 37842492
thank you all for your help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now