help with query(not easy) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with query(not easy)

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-28 : 05:36:27

i have to table(SQL 2005)

TRANSORDER,CUSTOMERS
i want to count each customer just one in month
and to get the total (and still count each customer just one)
Example:
i have :

customerNo  MONTH     YEAR

123            1       2007
123            1       2007
456            1       2007
456            1       2007
789            1       2007


123            2       2007
456            2       2007
789            2       2007
999            2       2007

i want to get
                 
       Count  MONTH   YEAR
         3        1     2007
         4        2     2007
        ---------------------
TOTATL   4              2007

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-28 : 06:00:55

if you have another option how to show it i glade to see it:

it need to ble like that or somthing close to this:

     
      Count MONTH  YEAR
        3      1    2007
        4      2    2007
---------------------
TOTATL 4 2007 
        4      3    2008
        8      5    2008
---------------------
TOTATL 8 2008

hey001us
Posting Yak Master

185 Posts

Posted - 2008-12-28 : 16:25:36

try this.
SELECT COUNT(customerNo) [Count], [Month], [Year]
From CUSTOMERS c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
GROUP BY [Month], [Year]

hey

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-28 : 22:52:21

SELECT COUNT(DISTINCT customerNo) [Count], [Month], [Year]
From CUSTOMERS c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
GROUP BY [Month], [Year]

--For Total
SELECT COUNT(DISTINCT customerNo) [Count], [Year]
From CUSTOMERS c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
GROUP BY [Year]

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-28 : 22:53:50

select count(distinct customerno) as customerno,month,year
from urtable group by month,year

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-29 : 00:09:31

use compute u will get the sum of count

select count(distinct customerno) as customerno,month,year
from customer group by month,year compute sum(count(distinct customerno))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 00:41:06

if this is for reporting purpose, you can very easily generate the reqd output format in your reporting application using grouping. you just need to get individual figures alone from the query behind. the aggregation can be done inside report for subtotylas and totals

wisdomtree2009
Starting Member

5 Posts

Posted - 2008-12-29 : 01:12:07

Hi Visakh.
I dont know whether this is permitted on this forum.
Do you mind to reply if i mail you.
I have tried to do it via this forum .
Incase if you have no problem to reply to my mail
pls drop a blank mail to surya.c8 at gmail com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 01:14:10

Although it is permitted to email forum members, it is best if you just post your questions or statements here so that you have more visibility to more members rather than just one person.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 01:16:43

quote:
Originally posted by wisdomtree2009

Hi Visakh.
I dont know whether this is permitted on this forum.
Do you mind to reply if i mail you.
I have tried to do it via this forum .
Incase if you have no problem to reply to my mail
pls drop a blank mail to surya.c8 at gmail com

As Tara suggested post question here so that it will increase visibility and more people will be able to help you.Make sure you give info on how your data is and how you want to show it.

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-29 : 01:39:36

thanks to everyone.

how can i do it in one query?

SELECT COUNT(DISTINCT customerNo) [Count], [Month], [Year]
From CUSTOMERS c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
GROUP BY [Month], [Year]

--For Total
SELECT COUNT(DISTINCT customerNo) [Count], [Year]
From CUSTOMERS c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
GROUP BY [Year]

to visakh16 i suppose that need to be in RS,if yes so i can make query in total ?

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-29 : 01:45:44

how query should be if i want it like that?
1-12 Month
2000-2008 Year
in the table is count(ditinct CustomerNo) By month and year
in total is count(Disinct CustomerNo) By Total

    
      1   2   3   4   5   6   7   8   9   10   11   12   TOTAL

2000  
2001                    123  
2002        12
.
.
.
2008                                      12

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-29 : 01:55:12

IF ur using SQL 2005 Version

U Can use PIVOT Operator to get the result in the format u specified

Jai Krishna

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-29 : 02:02:59

can you help me how to use with pivot?

and how can i make the total (second query)?

thanks

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-29 : 02:23:01

How i can make it to PIVOT?


     1  2   3 .....12

2000
...
2008

SELECT d.Month,d.Year,Sum(Number)As Number
FROM(
      SELECT  t.Month,t.Year,Count(DISTINCT t.CustomerNo) As Number
     FROM TRANSORDER t
     GROUP BY t.Month,t.Year,t.CustomerNo ) d
GROUP BY d.Month,d.Year

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 04:09:24

is it for reporting purpose? are you using sql reporting services?

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-29 : 04:13:36

now i dont know,but i want to know how to make the query with pivot,because i never use it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 04:25:16

quote:

Originally posted by inbs

How i can make it to PIVOT?


     1  2   3 .....12

2000
...
2008

SELECT d.Month,d.Year,Sum(Number)As Number
FROM(
      SELECT  t.Month,t.Year,Count(DISTINCT t.CustomerNo) As Number
     FROM TRANSORDER t
     GROUP BY t.Month,t.Year,t.CustomerNo ) d
GROUP BY d.Month,d.Year

even without pivot, you can get it


SELECT  [Year],
COUNT(DISTINCT CASE WHEN [Month]=1 THEN customerNo ELSE 0 END) AS [1],
COUNT(DISTINCT   CASE WHEN [Month]=2 THEN  customerNo ELSE 0 END) AS [2],
COUNT(DISTINCT  CASE WHEN [Month]=3 THEN customerNo ELSE 0 END) AS [3],
COUNT(DISTINCT  CASE WHEN [Month]=4 THEN customerNo ELSE 0 END) AS [4],
COUNT(DISTINCT CASE WHEN [Month]=5 THEN  customerNo ELSE 0 END) AS [5],
COUNT(DISTINCT CASE WHEN [Month]=6 THEN  customerNo ELSE 0 END) AS [6],
COUNT(DISTINCT CASE WHEN [Month]=7 THEN  customerNo ELSE 0 END) AS [7],
COUNT(DISTINCT CASE WHEN [Month]=8 THEN  customerNo ELSE 0 END) AS ,
COUNT(DISTINCT CASE WHEN [Month]=9 THEN  customerNo ELSE 0 END) AS [9],
COUNT(DISTINCT CASE WHEN [Month]=10 THEN  customerNo ELSE 0 END) AS [10],
COUNT(DISTINCT CASE WHEN [Month]=11 THEN customerNo ELSE 0 END) AS [11],
COUNT(DISTINCT CASE WHEN [Month]=12 THEN customerNo ELSE 0 END) AS [12],
COUNT(DISTINCT customerNo) AS [Total]
From CUSTOMERS c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
GROUP BY  [Year]

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-29 : 04:42:11

visakh16-tou are the BEST.

i dont think of that.

why you dont use pivot?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-29 : 04:47:02

quote:

Originally posted by visakh16

quote:

Originally posted by inbs

How i can make it to PIVOT?


     1  2   3 .....12

2000
...
2008

SELECT d.Month,d.Year,Sum(Number)As Number
FROM(
      SELECT  t.Month,t.Year,Count(DISTINCT t.CustomerNo) As Number
     FROM TRANSORDER t
     GROUP BY t.Month,t.Year,t.CustomerNo ) d
GROUP BY d.Month,d.Year

even without pivot, you can get it


SELECT  [Year],
COUNT(DISTINCT CASE WHEN [Month]=1 THEN customerNo ELSE 0 END) AS [1],
COUNT(DISTINCT   CASE WHEN [Month]=2 THEN  customerNo ELSE 0 END) AS [2],
COUNT(DISTINCT  CASE WHEN [Month]=3 THEN customerNo ELSE 0 END) AS [3],
COUNT(DISTINCT  CASE WHEN [Month]=4 THEN customerNo ELSE 0 END) AS [4],
COUNT(DISTINCT CASE WHEN [Month]=5 THEN  customerNo ELSE 0 END) AS [5],
COUNT(DISTINCT CASE WHEN [Month]=6 THEN  customerNo ELSE 0 END) AS [6],
COUNT(DISTINCT CASE WHEN [Month]=7 THEN  customerNo ELSE 0 END) AS [7],
COUNT(DISTINCT CASE WHEN [Month]=8 THEN  customerNo ELSE 0 END) AS ,
COUNT(DISTINCT CASE WHEN [Month]=9 THEN  customerNo ELSE 0 END) AS [9],
COUNT(DISTINCT CASE WHEN [Month]=10 THEN  customerNo ELSE 0 END) AS [10],
COUNT(DISTINCT CASE WHEN [Month]=11 THEN customerNo ELSE 0 END) AS [11],
COUNT(DISTINCT CASE WHEN [Month]=12 THEN customerNo ELSE 0 END) AS [12],
COUNT(DISTINCT customerNo) AS [Total]
From CUSTOMERS c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
GROUP BY  [Year]

SELECT [Year],
COUNT(DISTINCT CASE WHEN [Month]=1 THEN customerNo ELSE NULL END) AS [1],
COUNT(DISTINCT CASE WHEN [Month]=2 THEN customerNo ELSE NULL END) AS [2],
COUNT(DISTINCT CASE WHEN [Month]=3 THEN customerNo ELSE NULL END) AS [3],
COUNT(DISTINCT CASE WHEN [Month]=4 THEN customerNo ELSE NULL END) AS [4],
COUNT(DISTINCT CASE WHEN [Month]=5 THEN customerNo ELSE NULL END) AS [5],
COUNT(DISTINCT CASE WHEN [Month]=6 THEN customerNo ELSE NULL END) AS [6],
COUNT(DISTINCT CASE WHEN [Month]=7 THEN customerNo ELSE NULL END) AS [7],
COUNT(DISTINCT CASE WHEN [Month]=8 THEN customerNo ELSE NULL END) AS

,
COUNT(DISTINCT CASE WHEN [Month]=9 THEN customerNo ELSE NULL END) AS [9],
COUNT(DISTINCT CASE WHEN [Month]=10 THEN customerNo ELSE NULL END) AS [10],
COUNT(DISTINCT CASE WHEN [Month]=11 THEN customerNo ELSE NULL END) AS [11],
COUNT(DISTINCT CASE WHEN [Month]=12 THEN customerNo ELSE NULL END) AS [12],
COUNT(DISTINCT customerNo) AS [Total]
From CUSTOMER c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
GROUP BY [Year]

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 04:50:07

quote:
Originally posted by inbs

visakh16-tou are the BEST.

i dont think of that.

why you dont use pivot?

you can use Pivot as well. in that case, it will be like:-


SELECT *
FROM
(
SELECT customerNo, [Month], [Year]
From CUSTOMERS c
JOIN TRANSORDER t ON c.customerNo = t.customerNo
)m
PIVOT (COUNT(DISTINCT customerNo) FOR Month IN ([1],[2],[3],...,[12]))p

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources