CODEWITHSUNDEEP
sql-server
user6529973
user6529973

Reputation:

How to display multiple column with different condition in one query in SQL?

I want to know how many distinct TRANSPORTNO for each months from table TRANSPORTARCHIVE. There is probably a better way to do this.

select COUNT(distinct TRANSPORTNO)
from TRANSPORTARCHIVE
where DATEASKED between '2015-01-01' and '2015-01-31'

select COUNT(distinct TRANSPORTNO)
from TRANSPORTARCHIVE
where DATEASKED between '2015-02-01' and '2015-02-28'

select COUNT(distinct TRANSPORTNO)
from TRANSPORTARCHIVE
where DATEASKED between '2015-03-01' and '2015-03-31'

and so on...

I want an output that show 12 columns with the number of distinct TRANSPORTNO for every month

Thanks

Upvotes: 0

Views: 78

Answers (3)

Lewis Worley
Lewis Worley

Reputation: 279

Aleksey's answer is essentially correct, except you need to get rid of the WHERE clause. 

SELECT
       COUNT(distinct TRANSPORTNO) as cnt,
       month(DATEASKED) as date_month
FROM TRANSPORTARCHIVE
GROUP BY month(DATEASKED);

Upvotes: 0

Aleksey Ratnikov
Aleksey Ratnikov

Reputation: 569

Try this one:

select 
   COUNT(distinct TRANSPORTNO) as cnt,
   month(DATEASKED) as date_month
from TRANSPORTARCHIVE
group by month(DATEASKED)
where DATEASKED between '2015-01-01' and '2015-01-31';

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169274

You want to group by the month. Something like this in MySQL:

select
    EXTRACT(year from DATEASKED) 
    , EXTRACT(month from DATEASKED) 
    , COUNT(distinct TRANSPORTNO)
from 
    TRANSPORTARCHIVE
where 
    DATEASKED between '2015-01-01' and '2015-03-31'
group by 
    1,2

If you're using SQL Server:

select
    YEAR(DATEASKED) 
    , MONTH(DATEASKED) 
    , COUNT(distinct TRANSPORTNO)
from 
    TRANSPORTARCHIVE
where 
    DATEASKED between '2015-01-01' and '2015-03-31'
group by 1,2

Upvotes: 0

Related Questions