CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
DooDoo
DooDoo

Reputation: 13447

Get record Count and retrieve some values from other tables In result set

Please consider this scenario:

I have three table like this : My main table that contains details data :

 ID     CityCode          Switch1      Switch2      Price          Desc
 --------------------------------------------------------------------------
 1        202               10            1         2342        Some Desc     
 2        202               10            1          12         Some Desc
 3        202               12            1          22         Some Desc
 4        203               10            1          46         Some Desc
 5        203               12            1          23         Some Desc
 6        203               12            1          12         Some Desc
 7        205               14            1         6758        Some Desc

ID is Identity coulmn and is Primary Key.

Another table is CityTypes:

CityCode            CityName             CityType
--------------------------------------------------
  202                City 1                 1
  203                City 2                 2
  204                City 3                 1
  205                City 4                 1

and third table is TotalCount :this table show Total count record should insert for a specific good in specific City.

 Switch1      Switch2       Name           CityType       TotalCount
 -------------------------------------------------------------------
 10            1           Good 1              1             10
 10            1           Good 1              2             5
 10            1           Good 1              3             3
 11            1           Good 2              1             12
 11            1           Good 2              2             8
 11            1           Good 2              3             5
 12            1           Good 3              1             10
 12            1           Good 3              2             5
 12            1           Good 3              3             3
 13            1           Good 4              1             10
 13            1           Good 4              2             5
 13            1           Good 4              3             3
 14            1           Good 5              1             10
 14            1           Good 5              2             5
 14            1           Good 5              3             3

Switch1+Switch2+CityType are primary key

I want to write a query that return this result:

CityName      GoodName      InsertedCount     TotalCount    InsertedCount-TotalCount
------------------------------------------------------------------------------------
  City 1      Good 1              2              10                   -8

I don't know How I can relate this tables for this query. I wrote this Query:

SELECT CityCode,Switch2,Switch1,COUNT(ID)
FROM   tblMain
GROUP BY  CityCode,Switch2,Switch1
ORDER BY CityCode,Switch2,Switch1

but I don't know How I can relate this to others tables

Upvotes: 0

Views: 290

Answers (2)

NG.
NG.

Reputation: 6043

Check this out

select ct.cityname, tc.GoodName,COUNT() as InsertedCount,sum(tc.totalcount) as TotalCount, COUNT()-sum(tc.totalcount) as 'InsertedCount-TotalCount' FROM tblmain tm left join CityTypes ct on ct.citycode = tm.citycode left join totalcount tc on tc.citytype = ct.citytype and tm.switch1=tc.switch1 and tm.switch2=tc.switch2 group by ct.cityname, tc.GoodName

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try this

SELECT 
    ct.Cityname,total.name,COUNT(total.*) as insertedcount,sum(total.totalcount) as totalcount, 
    COUNT(total.*)-sum(total.totalcount) as diff
FROM   
    tblMain as main inner join citytypes as ct on main.citycode=ct.citycode
    inner join totalcount as total on ct.citytype=total.citytype
    and main.switch1=total.switch1 and main.switch2=total.switch2 
GROUP BY  ct.Cityname,total.name
ORDER BY ct.Cityname,total.name

Upvotes: 1

Related Questions