Results 1 to 2 of 2

Thread: forming a query with functions

  1. #1
    Join Date
    Apr 2009
    Posts
    7

    forming a query with functions

    here is a the entire details of the table with the expected output

    Table name= Billdeatils

    billno customer basicamt ta date location

    1005 200 10
    1008 500 15
    1003 800 20
    2015 1000 10
    2015 300 12
    1008 200 18
    1003 1500 15
    1005 2000 16
    2015 400 20
    2010 500 50
    1003 600 25
    1000 300 20

    toral record count =12

    query for getting the below output

    1) count (disctinct billno) = 7
    2) sum (basicamt + ta) of all 12 records

    3) for each bill i need i need count & sum
    eg billno =1005

    count(billno) where billno =1005 shld give 2
    sum (basicamt + ta) where billno =1005 shld give 2226


    4) details of each bill
    eg billno =1005

    1005 200 10
    1005 2000 16

    the below is the quer y but

    SELECT DISTINCT billno,(SELECT COUNT(DISTINCT billno) FROM billdetails) AS ccount,
    (SELECT sum(basicamt+ta) FROM billdetails) AS amt,
    (SELECT COUNT( billno) FROM billdetails where billno=1005) AS paycount,
    (SELECT sum(basicamt+ta) FROM billdetails where billno=1005)
    AS payamt,
    (SELECT * FROM billdetails where billno=1005)
    FROM billdetails

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You should write query for each of those requests.

Posting Permissions

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