none
sql query

    Question

  • I'm trying to join to tables and get counts but not sure if this is right.

    Table1 has columns -  name, model, os, memory, location

    Table2 has columns -  location, description

    This is output I'm trying to get based on model and os for the count;

    select Table1.location, Table1, model, Table2.description

    from Table1, Table2

    where Table1.model = 1

    order by Table1.location

    group by Table1.model;

    Thursday, January 16, 2014 3:24 PM

Answers

  • This is wat yur looking for ?
    select
    t2.location,t2.building,t2.floor,t2.description,count(*) as count
    from table2 t2 
    inner join table1 t1 on t2.Location=t1.Location
    where 
    t1.os='Windows XP' and 
    t1.Model in ('Optiplex 980','Optiplex 990') and
    t1.memory>=4000
    group by t2.location,t2.building,t2.floor,t2.description

    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    Thursday, January 16, 2014 6:14 PM
  • select t2.*,COALESCE(t1.WorkstationCount,0) AS WorkStationCount
    from Table2 t2
    LEFT JOIN (SELECT Location,
    COUNT(CASE WHEN Memory > 4000 AND OS = 'Windows XP' AND (Model LIKE '%980' OR MOdel LIKE '%990') THEN WorkstationName ELSE NULL END) AS WorkstationCount
    FROM Table1 
    GROUP BY Location
    )t1  
    on t2.location = t1.location

    If you dont want to list those locations without any workstations at all make it into an INNER JOIN

    select t2.*,COALESCE(t1.WorkstationCount,0) AS WorkStationCount
    from Table2 t2
    INNER JOIN (SELECT Location,
    COUNT(CASE WHEN Memory > 4000 AND OS = 'Windows XP' AND (Model LIKE '%980' OR MOdel LIKE '%990') THEN WorkstationName ELSE NULL END) AS WorkstationCount
    FROM Table1 
    GROUP BY Location
    )t1  
    on t2.location = t1.location


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Thursday, January 16, 2014 6:32 PM

All replies

  • Try the below:

    select  location,model,Os,count(1)Over (partition by  model,os) cnt
    from Table1 A
    Inner join  Table2 B on A.location=B.location
    where Table1.model = 1
    Order by a.location



    Thursday, January 16, 2014 3:32 PM
  • try this:

    DECLARE @Table1 TABLE (name VARCHAR(20), model INT, os INT, memory INT, location INT)
    DECLARE @Table2 TABLE (location INT, description VARCHAR(20))
    INSERT INTO @Table1 VALUES ('test1', 1, 1, 1, 1), ('test2', 1, 1, 1, 1), ('test3', 1, 2, 1, 2)
    INSERT INTO @Table2 VALUES (1, 'place1'), (2, 'place2')
    --query
    SELECT
    	A.location,
    	A.model,
    	B.description,
    	COUNT(*) as count
    FROM
    	@Table1 A INNER JOIN @Table2 B ON A.location = B.location
    WHERE
    	A.model = 1
    GROUP BY
    	A.location, A.model, B.description
    ORDER BY
    	A.model

    Thursday, January 16, 2014 3:42 PM
  • what if where Table1.model in (1,2,3) instead of just 1.
    Thursday, January 16, 2014 4:11 PM
  • select t1.model, t1.os,count(*)
    from Table1 t1
    inner join  Table2 t2
    on t2.location = t1.location
    where Table1.model IN (1,2,3)
    group by t1.model,t1.os
    
    
    If this is not what you expect please post some sample data and show us output you require out of it.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, January 16, 2014 4:21 PM
  • Table1

    WorstationName   Model               OS                   Memory  Location

    computer1             Optiplex 980     Windows XP   2048       123

    computer2             Optiplex 990     Windows 7     4096        123

    Computer3            Optiplex 620     Windows XP    4096       124

    computer4              optiplex 980    windows xp      4096      123

    Table2

    Location     building  floor   description

    123           north     2         north building

    124           south    1          south building

    So the output that I'm looking for like this;

    location/building/floor/desciption on row

    count of workstationname where os=xp and model in 980, 990 and memory >=4000 in column.

    Hope that makes sense.

    Thursday, January 16, 2014 4:48 PM
  • This is wat yur looking for ?
    select
    t2.location,t2.building,t2.floor,t2.description,count(*) as count
    from table2 t2 
    inner join table1 t1 on t2.Location=t1.Location
    where 
    t1.os='Windows XP' and 
    t1.Model in ('Optiplex 980','Optiplex 990') and
    t1.memory>=4000
    group by t2.location,t2.building,t2.floor,t2.description

    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>


    Thursday, January 16, 2014 6:14 PM
  • select t2.*,COALESCE(t1.WorkstationCount,0) AS WorkStationCount
    from Table2 t2
    LEFT JOIN (SELECT Location,
    COUNT(CASE WHEN Memory > 4000 AND OS = 'Windows XP' AND (Model LIKE '%980' OR MOdel LIKE '%990') THEN WorkstationName ELSE NULL END) AS WorkstationCount
    FROM Table1 
    GROUP BY Location
    )t1  
    on t2.location = t1.location

    If you dont want to list those locations without any workstations at all make it into an INNER JOIN

    select t2.*,COALESCE(t1.WorkstationCount,0) AS WorkStationCount
    from Table2 t2
    INNER JOIN (SELECT Location,
    COUNT(CASE WHEN Memory > 4000 AND OS = 'Windows XP' AND (Model LIKE '%980' OR MOdel LIKE '%990') THEN WorkstationName ELSE NULL END) AS WorkstationCount
    FROM Table1 
    GROUP BY Location
    )t1  
    on t2.location = t1.location


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Thursday, January 16, 2014 6:32 PM
  • I tried your query but getting type mismatch

    select
    t2.location,t2.building,t2.floor,t2.description,count(*) as count
    from table2 t2 
    inner join table1 t1 on t2.Location=t1.Location
    where 
    t1.os='Windows XP' and 
    t1.Model in ('Optiplex 980','Optiplex 990') and
    t1.memory>=4000
    group by t2.location,t2.building,t2.floor,t2.description
    Friday, January 24, 2014 6:18 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you did not; total failure). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    Why are you this rude when you want other people help you? Do you work for people who treat you like this, so this is the only way you know to behave to others? 

    >> I'm trying to join to tables and get counts but not sure if this is right. <<

    Here is a skeleton for you to add flesh: 

    CREATE TABLE Computers
    (computer_name VARCHAR(??) NOT NULL
     model_nbr CHAR(??) NOT NULL
     os_name VARCHAR(??) NOT NULL,
     memory_size INTEGER NOT NULL,
     machine_location CHAR(??) NOT NULL
      REFERENCES Locations
      ON UPDATE CASCADE);

    CREATE TABLE Locations
    (machine_location CHAR(??) NOT NULL PRIMARY KEY,
     location_description VARCHAR(100) NOT NULL);

    >> This is output I'm trying to get based on model and os for the count; <<

    What does that mean? Here is my guess: 

    SELECT C.model_nbr, C.os_name, COUNT(*) AS machine_cnt
      FROM Computers AS C 
    GROUP BY CUBE (C.model_nbr, C.os_name); 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, January 24, 2014 6:53 PM
  • alex,

    would be helpful if you could exactly let us know what issue you are facing. Post the query + error message pls .. 

    Thanks


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Saturday, January 25, 2014 6:42 PM