Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Unanswered: Query Help

    Hello, I have 2 queries. I need help to join them properly where I can add column CONSUMP from query 2 to query 1 as the last column.

    I have query 1

    SELECT dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC, COUNT (dbo.PROPDB_UMMTR.XSIZE) AS QUANTITY
    FROM dbo.PROPDB_UMMTR INNER JOIN
    dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY INNER JOIN
    dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE INNER JOIN
    dbo.PROPDB_CUST ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY INNER JOIN
    dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY
    WHERE (dbo.PROPDB_CUST.XSTATUS = 'AC') AND (dbo.PROPDB_CUST.CUSTNAME <> 'VACANT')
    GROUP BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMSIZ.DESCRIPT
    ORDER BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE

    Output

    DISTRICT......RATETYPE......DESCRIPT......XSIZE... ...SIZEDESC......QUANTITY
    1.................I01................RES 5/8........1............1 INCH...........10
    2.................I02................RES 1...........1............1 INCH..........20
    3.................I03................RES 3...........3............3 INCH...........5
    4.................I04................RES 2...........2............2 INCH..........30

    I have query 2

    SELECT dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE,
    dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC, SUM(dbo.PROPDB_USAGO.CONSUMED)
    AS CONSUMPTION
    FROM dbo.PROPDB_UMMTR INNER JOIN
    dbo.PROPDB_USAGO ON dbo.PROPDB_UMMTR.METERKY = dbo.PROPDB_USAGO.METERKY INNER JOIN
    dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY INNER JOIN
    dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE INNER JOIN
    dbo.PROPDB_CUST ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY INNER JOIN
    dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY
    WHERE (dbo.PROPDB_CUST.XSTATUS = 'AC') AND (dbo.PROPDB_CUST.CUSTNAME <> 'VACANT')
    GROUP BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_TRNCD.DESCRIPT,
    dbo.PROPDB_UMSIZ.DESCRIPT
    ORDER BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE

    Output

    DISTRICT......RATETYPE......DESCRIPT......XSIZE... ...SIZEDESC......CONSUMP
    1.................I01................RES 5/8........1............1 INCH...........10000
    2.................I02................RES 1...........1............1 INCH..........20000
    3.................I03................RES 3...........3............3 INCH...........5000
    4.................I04................RES 2...........2............2 INCH..........30000


    Query with wrong count

    SELECT dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE,
    dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC, COUNT (dbo.PROPDB_UMMTR.XSIZE) AS QUANTITY, SUM(dbo.PROPDB_USAGO.CONSUMED)
    AS CONSUMPTION
    FROM dbo.PROPDB_UMMTR INNER JOIN
    dbo.PROPDB_USAGO ON dbo.PROPDB_UMMTR.METERKY = dbo.PROPDB_USAGO.METERKY INNER JOIN
    dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY INNER JOIN
    dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE INNER JOIN
    dbo.PROPDB_CUST ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY INNER JOIN
    dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY
    WHERE (dbo.PROPDB_CUST.XSTATUS = 'AC') AND (dbo.PROPDB_CUST.CUSTNAME <> 'VACANT')
    GROUP BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_TRNCD.DESCRIPT,
    dbo.PROPDB_UMSIZ.DESCRIPT
    ORDER BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE

    output

    shows results as query 2 with count being way off.

    DISTRICT..RATETYPE..DESCRIPT..XSIZE..SIZEDESC..COU NT....CONSUMP
    1.............I01............RES 5/8....1.........1 INCH......10...........10000
    2.............I02............RES 1.......1.........1 INCH......20...........20000
    3.............I03............RES 3.......3.........3 INCH.......5............5000
    4.............I04............RES 2.......2.........2 INCH......30...........30000


    Any help is appreciated.
    Thank you,
    Last edited by lgonzales@coe; 10-31-11 at 18:44. Reason: spacing

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    Re: Query Help

    You've included dbo.PROPDB_UMMTR.XSIZE both as a GROUP BY and an aggregate - COUNT(dbo.PROPDB_UMMTR.XSIZE). What if you left the COUNT column, but, excluded the preceding column, like so...

    Code:
    SELECT
      dbo.PROPDB_UMMTR.DISTRICT
     ,dbo.PROPDB_UMMTR.RATETYPE
     ,dbo.PROPDB_TRNCD.DESCRIPT
     -- ,dbo.PROPDB_UMMTR.XSIZE
     ,dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC
     ,COUNT(dbo.PROPDB_UMMTR.XSIZE) AS QUANTITY
     ,SUM(dbo.PROPDB_USAGO.CONSUMED) AS CONSUMPTION
    FROM
      dbo.PROPDB_UMMTR
    INNER JOIN
      dbo.PROPDB_USAGO ON dbo.PROPDB_UMMTR.METERKY = dbo.PROPDB_USAGO.METERKY
    INNER JOIN
      dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY
    INNER JOIN
      dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE
    INNER JOIN
      dbo.PROPDB_CUST  ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY
    INNER JOIN
      dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY
    WHERE
      dbo.PROPDB_CUST.XSTATUS = 'AC' AND dbo.PROPDB_CUST.CUSTNAME <> 'VACANT'
    GROUP BY
      dbo.PROPDB_UMMTR.DISTRICT
     ,dbo.PROPDB_UMMTR.RATETYPE
     ,dbo.PROPDB_TRNCD.DESCRIPT
     -- ,dbo.PROPDB_UMMTR.XSIZE
     ,dbo.PROPDB_UMSIZ.DESCRIPT
    ORDER BY
      dbo.PROPDB_UMMTR.DISTRICT
     ,dbo.PROPDB_UMMTR.RATETYPE
     ,dbo.PROPDB_TRNCD.DESCRIPT
     -- ,dbo.PROPDB_UMMTR.XSIZE

  3. #3
    Join Date
    Oct 2011
    Posts
    5

    Query Help

    homerow,
    Thank you for your reply. However, the quantity is still way off. The only difference is that the column for size is removed from the query.

  4. #4
    Join Date
    Oct 2011
    Posts
    5

    Query Help

    If I use the UNION, it king of works but everything is entered into one column for the Quantity and Consumption columns. I need the results to show in 2 different columns.

  5. #5
    Join Date
    Oct 2011
    Posts
    5

    Query Help

    Doesn't anyone have any suggestions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •